topic Re: Rank Function in QlikView

Rank Function

Mon, 16 Dec 2013 15:14:15 GMT

Hi Everyone,

I am working on selecting the highest performer in a certain type of sport. So, I need my text object to return a name for the highest number of scores, and currently the expression is

= only({<Sportsman={"=rank(sum([Total Number of Points Scored], Sportsman)=2"}>} Sportsman),however it doesn't work and only returns a '-'. Can anyone help with this please?

Thank you,

Anna

Re: Rank Function

Gysbert_Wassenaar — Mon, 16 Dec 2013 15:31:30 GMT

Try =FirstSortedValue(Sportsman,-aggr(sum([Total Number of Points Scored]),Sportsman)) for the name of the sportsman with the highest score. For the second highest use =FirstSortedValue(Sportsman,-aggr(sum([Total Number of Points Scored]),Sportsman),2)

Re: Rank Function

swuehl — Mon, 16 Dec 2013 15:35:06 GMT

There is a closing bracket ) missing in your advances search expression and I think you should remove the second argument from the rank() function:

= only({<Sportsman={"=rank(sum([Total Number of Points Scored]))=2"}>} Sportsman)

Re: Rank Function

Mon, 16 Dec 2013 16:10:42 GMT

Thanks, Gysbert, that worked for the highest one. What about the lowest?

Re: Rank Function

Gysbert_Wassenaar — Mon, 16 Dec 2013 16:15:30 GMT

remove the - character in front of the aggr.

Re: Rank Function

Mon, 16 Dec 2013 16:17:59 GMT

Thank you!

Re: Rank Function

Tue, 17 Dec 2013 09:36:18 GMT

what if I want to list all sportsmen that have the same highest value?

Re: Rank Function

tresB — Tue, 17 Dec 2013 09:44:37 GMT

In that case try with concat() function like:

= Concat({<Sportsman={"=rank(sum([Total Number of Points Scored]))=2"}>} Sportsman, ',')

Re: Rank Function

Tue, 17 Dec 2013 09:56:13 GMT

thank you for that. and the case for listing all sportsmen that have the same lowest value?

Re: Rank Function

tresB — Tue, 17 Dec 2013 10:04:34 GMT

This?

= Concat({<Sportsman={"=rank( - sum([Total Number of Points Scored]))=1"}>} Sportsman, ',')

Re: Rank Function

Tue, 17 Dec 2013 11:05:22 GMT

that doesn't seem quite to work. So, with the formula provided for the first one, if there is a 2, then it doesn't give the highest, but lists all second highest, so I changed it to 1 and it works., For the lowest one- 2,3 etc don't work..

Re: Rank Function

tresB — Tue, 17 Dec 2013 11:08:31 GMT

Did you notice the '-'symbol I used before sum() function for lowest rank? It should work. Otherwise try to share a sample app.

Re: Rank Function

Tue, 17 Dec 2013 11:12:04 GMT

I did notice the symbol, however, I can't share the app. It seems to return second highest values.