Announcements
cancel
Showing results for
Did you mean:
Partner - Specialist

## Ranking with two dimensions issue.

Good afternoon.

I am trying to get the ranking as well as money value of the top 5 items per site.

I have dimensions Site, Itname and a calculated dimension with the ranking. An there is 1 expressions summing the amounts. I use the same logic from the calculated dimension to sort the dimensions.

The ranking calculated dim is:  =if(aggr(rank(sum(Amt)),Site,Itname)<=5,aggr(rank(sum(Amt)),Site,Itname),null())

The expression for Amount is: sum(Amt).

The issue: Looking at the attached snippet you see there are 2 sites where the ranking goes wrong. I don't understand why it is correct for some sites but not all on them. If you can please let me know where I am going wrong.

Thank you.

Example of ranking

Labels (1)
• ### General Question

1 Solution

Accepted Solutions

Sorting pivots could become quite tricky. Maybe the following link (unfortunately it's archived and the link goes now to a cached version) is helpful (at least as starting-point for further investigations):

Perfectly Sorting Pivot Table (by A-Z. y-Value set... - Page 2 - Qlik Community - 1483393 (bingj.com...

Beside this you may also trying to transfer the logic from the (calculated) dimension into the expressions, for example with:

if(aggr(rank(sum(Amt)),Site,Itname)<=5,sum(Amt))

and similar with the rank (if showing this information is really important).

3 Replies

Have you the sorting also applied to the Itname dimension? Further I think the sorting could be just sum(Amt) without the aggr() which is only needed within the calculated dimension to enforce there the NULL for the unwanted ranks.

Partner - Specialist
Author

The closest I can get it:

Sort Site by Text.

ItName by =sum(Amt)

Ranking by =sum(Amt) or Numeric. Now there is only one site where the numbers don't work out.

Sorting pivots could become quite tricky. Maybe the following link (unfortunately it's archived and the link goes now to a cached version) is helpful (at least as starting-point for further investigations):

Perfectly Sorting Pivot Table (by A-Z. y-Value set... - Page 2 - Qlik Community - 1483393 (bingj.com...

Beside this you may also trying to transfer the logic from the (calculated) dimension into the expressions, for example with:

if(aggr(rank(sum(Amt)),Site,Itname)<=5,sum(Amt))

and similar with the rank (if showing this information is really important).