Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
pauluk__
Contributor III
Contributor III

Using Rank to get nth value

Hi all, I was wondering if you could help me with a query about getting ranked value n from a list.

Let's say I have a table of KPI_IDs which are either available in the selections or not. I would like to be able to get, for example, the KPI_ID value of the 3rd ranked selected value, which according to the below would be 5:

KPI_IDSelectedRanked Value
1
2YES1
3
4YES2
5YES3
6
7YES4

Do you know how I would be able to do this with the expression.

Thank you.

6 Replies
olivierrobin
Specialist III
Specialist III

hello

did you try to use a set analysis in your rank function  ?

Selected={'YES'}

pauluk__
Contributor III
Contributor III
Author

Hi Olivier,

This is more really to do with how I could get the value in the first column, as Rank on its own would return the third value. Outside a table, I would like to be able to know the KPI_ID value of, say, the third ranked value.

olivierrobin
Specialist III
Specialist III

well

i would do that in script. with a load and rank() by excluding Selected<>'YES'

basically, something

like

t1:

load kpi,sum(value) as v  group by kpi where selected='YES'

t2:

load kpi,rowno() resident t1 order by v descending

isingh30
Specialist
Specialist

You need this -

T.PNG

Thank You!

pauluk__
Contributor III
Contributor III
Author

Hi Ishtdeep,

Really I'm looking for an expression, outside a table that would answer the question 'Which is the third ranked KPI_ID value' and return 5 in the above example.

isingh30
Specialist
Specialist

C.PNG

only({<Selected = {'YES'}>}[Ranked Value])