Ranking with two dimensions issue.

[johngouws]

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

[marcus_sommer]

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.

[johngouws]

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. 

[marcus_sommer · Accepted solution]

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