
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Rank identical values
Hi All,
Apologies for posting this, but I have gone through every discussion with the word RANK in it and still not find the answer but I am sure there must be one to this question.
I have a pivot table with to dimensions: "Company" and "Salesman" and one expression which is as simple as they come: Count(Sales_ID) which gives me a count of the number of unique sales per Company and Salesman.
I then have wanted to rank them, so I would end up with something like:
Company Salesman Count of Sales Rank
ABC GH 23 1
ABC TR 21 2
ABC JP 21 3
DEF BN 54 1
DEF VC 32 2
DEF OK 32 3
However my problem is that the ranking function, regardless of how I use it (in various combinations with aggr function, etc) doesn't work like I would like it to work - ie: identical count values are ranked the same, so 21 has a rank of 2 for both salesmen for company ABC, and so on.
In SQL I would use a combination of Rank and Partition by to solve this issue, usually using something like a rowno etc to act as the decider when identical count values are present, but none of my attempts in Qlikview seem to have worked.
Could someone please tell me how I can break up my rankings so they are sequential and don't provide the same rank for identical values please?
Thanks,
Ross
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try: num(rank(CountOfSales,4))
talk is cheap, supply exceeds demand

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. Apologies for such a silly question but it was incredibly frustrating to not be able to find this answer. I usually do much of my work in SQL so Qlikview is relatively new to me.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Gysbert, I ran into this same problem and your solution worked. That being said, I'm not sure why, based on my reading of what the num function does. Can you please explain why this works for removing duplicates?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Gysbert, I ran into this same problem and your solution worked. That being said, I'm not sure why, based on my reading of what the num function does. Can you please explain why this works for removing duplicates?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Gysbert, I ran into this same problem and your solution worked. That being said, I'm not sure why, based on my reading of what the num function does. Can you please explain why this works for removing duplicates?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi folks!
Gysbert,
Thank you very much! Your answer helped me a lot this week.
Lewis (and anyone with the same question):
I read the following post linked to another Thread about this issue:
It seems that the use of NUM function in this case is a workaround for this necessity...
Qlik personnel,
Please, I would suggest you to include this information on the help/Manual Reference to the RANK function. It will save time for many developers in the future.
Best regards to all,
Marcos


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Some months have passed but, I used this to create a top 3 bottom 3 dimension, it is also handle if we have identical values (or calc. output).
=aggr(
if(
num(rank(aggr(sum(value),id),4)) >= max(total aggr(rowno(total),id),3) or
num(rank(aggr(sum(value),id),4)) <= min(total aggr(rowno(total),id),3)
,id
),id)
Dan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Wow!! superb solution
