Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
johngouws
Partner - Specialist
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 rankingExample of ranking

 

 

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

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

 

View solution in original post

3 Replies
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
Partner - Specialist
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. 

Example.PNG

marcus_sommer

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