9 Replies Latest reply: Apr 11, 2014 3:48 AM by Christian Conejero

# Simple Rank() question

Hi,

Say i have the following table:

ContributorPoints
James

300

Lisa290
Jake290
Kelly150
Sara123
Fidel110

In my eyes ranking the table should gives the following rank

ContributorPoints
James

1

Lisa2
Jake2
Kelly3
Sara4
Fidel5

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

• ###### Re: Simple Rank() question

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

• ###### Re: Simple Rank() question

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.

• ###### Re: Simple Rank() question

you can use : num(rank(Points,1)) in your case.

Thanks to HIC for this.

• ###### Re: Simple Rank() question

Rank(SUM(Points),0,1)

• ###### Re: Simple Rank() question

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.

• ###### Re: Simple Rank() question

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).
1Low value on all rows.
2Low value on first row, blank on the following rows.
• ###### Re: Simple Rank() question

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

• ###### Re: Simple Rank() question

Bingo! Thanks!!!

• ###### Re: Simple Rank() question

num(rank(Points1,4))