topic How to ignore calculated dimension using RANK function in QlikView

How to ignore calculated dimension using RANK function

Tue, 25 Jan 2011 20:27:34 GMT

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?

How to ignore calculated dimension using RANK function

Tue, 25 Jan 2011 20:57:53 GMT

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,1

How to ignore calculated dimension using RANK function

Sun, 27 May 2012 12:29:57 GMT

Thanks mate, It helped me a lot.

-Zubair