Using Rank to get nth value

[pauluk__]

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_ID	Selected	Ranked Value
1
2	YES	1
3
4	YES	2
5	YES	3
6
7	YES	4

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

Thank you.

[olivierrobin]

hello

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

Selected={'YES'}

[pauluk__]

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]

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]

You need this -

Thank You!

[pauluk__]

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]

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