Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Say i have the following table:
Contributor | Points |
---|---|
James | 300 |
Lisa | 290 |
Jake | 290 |
Kelly | 150 |
Sara | 123 |
Fidel | 110 |
In my eyes ranking the table should gives the following rank
Contributor | Points |
---|---|
James | 1 |
Lisa | 2 |
Jake | 2 |
Kelly | 3 |
Sara | 4 |
Fidel | 5 |
Using Rank() function, Qlikview will set Lisa and Jake both on position '2-3'.
Is it possible to give Jake and Lisa the well deserved second place ?(after all they work really hard for it
Dror
I tried to get consecutive ranking and I came to the following expression
Div(
Index(
'-' & Concat(DISTINCT TOTAL num(aggr(rank(Points,1,1),Name), '0000'), '-', -Points),
num(rank(Points,1,1),'0000' )
)
,5)+1
The Rank() function is Dual and all you need to do is to use Num(Rank(...)) to get what you want. You can also play around with the second parameter - mode - of the Rank function.
HIC
you can use : num(rank(Points,1)) in your case.
Thanks to HIC for this.
Rank(SUM(Points),0,1)
Hi,
Try use this:
num( rank( sum( Points ), 1))
In this mode will get the lowest rank for Lisa and Jake, in this case, 2.
Hi
Try like this
rank( sum( Points ), 1)
From help:
rank([ total ] expression [ , mode [, format ] ])
The second parameter mode specifies the number representation of the function result.
mode
0 (default) | If all ranks within the sharing group fall on the low side of the middle value of the entire ranking, all rows get the lowest rank within the sharing group. If all ranks within the sharing group fall on the high side of the middle value of the entire ranking, all rows get the highest rank within the sharing group. If ranks within the sharing group span over the middle value of the entire ranking, all rows get the value corresponding to the average of the top and bottom ranking in the entire column segment. |
1 | Lowest rank on all rows. |
2 | Average rank on all rows. |
3 | Highest rank on all rows. |
4 | Lowest rank on first row, then incremented by one for each row. |
The third parameter format specifies the text representation of the function result.
format
0 (default) | Low value&' - '&high value on all rows (e.g. 3 - 4). |
---|---|
1 | Low value on all rows. |
2 | Low value on first row, blank on the following rows. |
I tried to get consecutive ranking and I came to the following expression
Div(
Index(
'-' & Concat(DISTINCT TOTAL num(aggr(rank(Points,1,1),Name), '0000'), '-', -Points),
num(rank(Points,1,1),'0000' )
)
,5)+1
num(rank(Points1,4))
Bingo! Thanks!!!
Hi. I've just placed a topic about this funcktion.
The second parameter does not work.
The NAME Rank does it backwards.
-----------This text editor does not allow me to copy anything here. The topic was placed a while ago.
RANK FUNCTION DOES NOT WORK.
Do I need help or it is a bug?
Thanks.