Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

Try this

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


Capture.PNG

Creator II
Creator II

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?

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

Creator II
Creator II

Thanks a lot!!!

Creator II
Creator II

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...