Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
newqlik2017
Creator II
Creator II

Rank - List Top values and Group other values

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:

RAW TABLE
COUNTRYSTATEAMOUNT
USACA100
USATX125
USAFL200
USAPA60
USANY300
USANJ200
CANADABC150
CANADAON75
CANADAQC

50

   

RESULT TABLE
COUNTRY123OTHER
USANY300FL200NJ200ALL OTHERS285
CANADABC150ON75QC50ALL OTHERS-
1 Solution

Accepted Solutions
sunny_talwar

Here you are

Capture.PNG

Dimensions

COUNTRY

=Aggr(If(Rank(Sum(AMOUNT)) < 4, Num(Rank(Sum(AMOUNT), 4)), 'OTHERS'), COUNTRY, STATE)

Expressions

=Aggr(If(Rank(Sum(AMOUNT)) < 4, STATE, 'ALL OTHERS'), COUNTRY, STATE)

SUM(AMOUNT)

View solution in original post

6 Replies
sunny_talwar

Try this

=Aggr(If(Rank(Sum(AMOUNT)) < 4, STATE, 'ALL OTHERS'), COUNTRY, STATE)


Capture.PNG

newqlik2017
Creator II
Creator II
Author

Test1.JPG

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?

sunny_talwar

Here you are

Capture.PNG

Dimensions

COUNTRY

=Aggr(If(Rank(Sum(AMOUNT)) < 4, Num(Rank(Sum(AMOUNT), 4)), 'OTHERS'), COUNTRY, STATE)

Expressions

=Aggr(If(Rank(Sum(AMOUNT)) < 4, STATE, 'ALL OTHERS'), COUNTRY, STATE)

SUM(AMOUNT)

newqlik2017
Creator II
Creator II
Author

Thanks a lot!!!

newqlik2017
Creator II
Creator II
Author

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.

sunny_talwar

Ya, I guess for non-existent stuff, it might be difficult to populate this...