This field is required.
Only these extensions are allowed(.jpg, .JPG, .jpeg, .JPEG, .gif, .GIF, .png, .PNG)
Tags cannot contain the characters ' /, \\, #, ?, or ; >,< '
Only these extensions are allowed(.zip,.ZIP,.pdf,.PDF,.qvf,.QVF,.qvw,.QVW)
Discussion Board for collaboration related to QlikView App Development.
Looking for top 3 states:
1) If there are more than 3 states, all other states besides top 3 should be grouped into 'ALL OTHERS'
2) If there are 3 or less than 3 states, 'All OTHERS' should still be displayed but won't have any value
I used ranked function to create a calculated dimension but how do we create the result table as listed below.
Attached is a qvw with data for you reference. Any experts?
Below is the raw table and result table:
Here you are
=Aggr(If(Rank(Sum(AMOUNT)) < 4, Num(Rank(Sum(AMOUNT), 4)), 'OTHERS'), COUNTRY, STATE)
=Aggr(If(Rank(Sum(AMOUNT)) < 4, STATE, 'ALL OTHERS'), COUNTRY, STATE)
View solution in original post
Thanks Sunny. It's one step forward. I need to produce the final table as the Result table. While pivoting the state, the state is now a column but looking the states for each country to be a row and the corresponding values next to the state.
Any ideas how this can be done?
Thanks a lot!!!
Hi Sunny, in this same example (QVW) if I need to populate 'ALL OTHERS' value for state always how can we achieve that?
'ALL OTHERS' text gets populated if there are more than 3 states. However, If there are 3 or less than 3 states (for instance CANADA that has 3 states) it's blank.
Ya, I guess for non-existent stuff, it might be difficult to populate this...