Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
You may use =MAX(AGGR(RANK(SUM({<Customer=,Year={$(=Only(Year)-1)}>}Sales)),Customer))
in the second chart.
Best regards,
Cosmina
Hi,
You may use =MAX(AGGR(RANK(SUM({<Customer=,Year={$(=Only(Year)-1)}>}Sales)),Customer))
in the second chart.
Best regards,
Cosmina
That works well. Thank you!