topic Problem with Rank Function, Column Order in QlikView

Problem with Rank Function, Column Order

Wed, 25 Nov 2020 16:16:04 GMT

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)?

Re: Problem with Rank Function, Column Order

trdandamudi — Fri, 27 Jan 2017 20:03:35 GMT

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

Re: Problem with Rank Function, Column Order

MK_QSL — Fri, 27 Jan 2017 20:16:23 GMT

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.

Re: Problem with Rank Function, Column Order

maxgro — Fri, 27 Jan 2017 20:35:37 GMT

Remove the dimension limit

Replace the dimension Customer with

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

Re: Problem with Rank Function, Column Order

Tue, 31 Jan 2017 14:59:50 GMT

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.

Re: Problem with Rank Function, Column Order

Tue, 31 Jan 2017 15:01:52 GMT

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

Re: Problem with Rank Function, Column Order

Tue, 31 Jan 2017 15:04:16 GMT

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!