Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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):
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).
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.
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):
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).