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 and Totals

I'm having an issue using the Rank function in a pivot chart table. I'm trying to rank all customers according to their sales for the selected year and the previous year. But I only want to display the top 5. I could live with a chart that shows only the first five lines -- and I get the desired rankings when I do this. But the totals reflect ALL customers, not just the Top 5.

When I change the Dimension Limit to show only the largest 5, I get the totals I want, but the previous year ranking is now relative to the top 5 only.

Do you see a way to get the ranking on the whole set, but the totals for the Top 5 only? See attached which has explanatory notes.

Thank you.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi,

You may use =MAX(AGGR(RANK(SUM({<Customer=,Year={$(=Only(Year)-1)}>}Sales)),Customer))

in the second chart.

Best regards,

Cosmina

View solution in original post

2 Replies
Anonymous
Not applicable
Author

Hi,

You may use =MAX(AGGR(RANK(SUM({<Customer=,Year={$(=Only(Year)-1)}>}Sales)),Customer))

in the second chart.

Best regards,

Cosmina

Not applicable
Author

That works well. Thank you!