Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to ignore calculated dimension using RANK function

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?

error loading image

1 Solution

Accepted Solutions
Not applicable
Author

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



View solution in original post

2 Replies
Not applicable
Author

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



Not applicable
Author

Thanks mate, It helped me a lot.

-Zubair