Problem with Rank Function, Column Order

[#1]

Please see attached. The Rank() function returns the data I want to see as long as it's in the second (or greater) column position in the chart. If I move it to the first column position, it fixes the sort order in an undesirable fashion and I can't restore it. (Specifically it shows 10 through 1 in descending order when re-sorted, but not 1 through 10 as desired.)

The attachment uses the QlikView provided sample sales data.

Thanks.

BONUS QUESTION: How can I get the Total to show the total of only the values shown (Top 10 versus the whole set)?

[trdandamudi]

Please see the attached file and hope this resolves your issue...

[MK_QSL]

Use this for Rank

=Num(Rank(Total Sum(Sales),4))

For Total Sales u can use

SUM(Total Sales)

SUM(Sales)/SUM(TOTAL Sales) will give you % to Total Sales for each Customer.

[maxgro · Accepted solution]

Remove the dimension limit

Replace the dimension Customer with

     =if(aggr(rank(sum(Sales)),Customer)<=10,Customer)

[#5]

Thanks. That seems to work except re-sorting the sales eventually brings up sales from the bottom. The total as Sum of Rows works better.

[#6]

Thank you. Initially gives good values, but re-sorting doesn't work very well.

[#7]

This works best and re-sorting works as expected. The NULL value even works as a rollup of "Others" but is easily suppressed. Thank you!