Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Rank

Dear All,

i

want to display region wise top five customers sales and rest of other customer sales with label  "Others", when i writen rank with aggr in pivot table its giving wrong no. of customers and sales value.

please give a solution.

for your reference i attached the file, in that file contains 2 table green table is containing the requirement and blue table contans data for validation

Thanks in advance

chandu jak

1 Solution

Accepted Solutions
lironbaram
Partner - Master III
Partner - Master III

hi

check the attach example

if you use version 11 you can use the left table other wise use the middle table

View solution in original post

4 Replies
lironbaram
Partner - Master III
Partner - Master III

hi

check the attach example

if you use version 11 you can use the left table other wise use the middle table

Not applicable
Author

Hi Chandu,

in your expression you ranked along a wrong field: see below for correction:

=If(Aggr(Rank(Aggr(Sum([INV_N_QUANTITY_PS]), REGION_PS,DISTRIBUTOR_ID_PS),4,1), INV_C_MARKET_PS ,DISTRIBUTOR_ID_PS) <= 5,DISTRIBUTOR_ID_PS)

Regards, Roland

swuehl
MVP
MVP

And I think you could shorten the dimension expression a bit (inner aggr() not needed):

=Aggr(if(Rank(Sum([INV_N_QUANTITY_PS]),4,1 <=5,DISTRIBUTOR_ID_PS),INV_C_MARKET_PS,DISTRIBUTOR_ID_PS)

If you set the NULL representation in presentation tab to OTHERS, it looks quite nice.

Regards,

Stefan

Not applicable
Author

Thanks For All,

Actually i used wrong field Now i corrected, instead of using INV_C_MARKET_PS field i used  REGION_PS

Chandu