Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Forum - I know there is a solution to this but I can't figure it out. Usually I would use set analysis to filter out data in my expression but I don't believe I can with the RANK function.
See below and attached example. I simply trying to RANK my dimensions based on Quantity sold, but the problem is that since it's a pivot table I'm creating a calculated dimension to list my top 5 dimensions and bucket the rest in an "Others" category. So when I use the RANK function in my expression it ranks this "Others" dimension as if it were in my original dataset.
I want to ignore the "Others" row in my RANK. Something like
IF Name = 'Others' THEN null() ELSE RANK(Name)
With this the "Others" category will be dropped to the bottom and it will be ignored in the ranking. Any suggestions?
I figured it out. I used nested Set Analysis to only rank the top 5 Products, basically ignoring the calculated dimension. I forget the power of set analysis sometimes.
Hope this example helps somebody. Just a note, I also added the rank expression below to my SORT expression so that the calculated dimension "Others" would show up last like it does in a regular straight table.
=
rank(sum({$<[Productname]={"=rank(sum([Quantity])) <= 5"}>} Quantity),4,1I figured it out. I used nested Set Analysis to only rank the top 5 Products, basically ignoring the calculated dimension. I forget the power of set analysis sometimes.
Hope this example helps somebody. Just a note, I also added the rank expression below to my SORT expression so that the calculated dimension "Others" would show up last like it does in a regular straight table.
=
rank(sum({$<[Productname]={"=rank(sum([Quantity])) <= 5"}>} Quantity),4,1Thanks mate, It helped me a lot.
-Zubair