topic Re: Ranking with two dimensions issue. in App Development

Ranking with two dimensions issue.

johngouws — Wed, 08 Mar 2023 13:22:42 GMT

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

Re: Ranking with two dimensions issue.

marcus_sommer — Wed, 08 Mar 2023 13:41:26 GMT

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.

Re: Ranking with two dimensions issue.

johngouws — Wed, 08 Mar 2023 14:11:14 GMT

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.

Re: Ranking with two dimensions issue.

marcus_sommer — Wed, 08 Mar 2023 14:38:18 GMT

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