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

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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

104546.jpg

View solution in original post

9 Replies
hic
Former Employee
Former Employee

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

Not applicable
Author

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

Thanks to HIC for this.

MK_QSL
MVP
MVP

Rank(SUM(Points),0,1)

Not applicable
Author

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.

MayilVahanan

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.
1Lowest rank on all rows.
2Average rank on all rows.
3Highest rank on all rows.
4Lowest 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.
Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
maxgro
MVP
MVP

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

104546.jpg

nizamsha
Specialist II
Specialist II

num(rank(Points1,4))

Not applicable
Author

Bingo! Thanks!!!

christian77
Partner - Specialist
Partner - Specialist

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.