Rank function as expression

[#1]

Hi,

I'm wondering if someone can help me on an issue that I'm having with Rank() function on Qlikview.
I'm attempting to create a report which returns the 2nd highest ID for an item as a calculated dimension.

e.g Item 123 has 3 seperate ids, 0124, 0234, 0563

So I want my table to return

Item   ID     Rank
123    0234   2

I've tried using the expression below
=aggr(rank(sum(ID)), Item)

However this only returns me the max ID = 0563.  The other 2 don't even appear in the table.

Item   ID     Rank
123    0563   1

When I try =aggr(rank(sum(ID))=2, Item) I get

Item   ID     Rank
123    0   2

So the ID isn't appearing

when I use
=aggr(rank(sum(Item)), ID)

it returns all 3 but with the rank as 1-3 for each instance.

Item   ID     Rank
123    0234   1-3
123    0124   1-3
123    0563   1-3

Can anyone help with this?
Any help is very much appreciated.

Thanks

[anbu1984]

Dim: Item

Expr: Max(ID,2)

[#3]

Thanks for the reply

When I use that expression it returns null but if I use maxstring(Id) it returns the highest value.

If I try maxstring(ID, 2) there is an error in the expression.

Do you know how to return the 2nd highest using maxstring?

[anbu1984]

Is ID numeric field? Then Max() should work.

Maxstring() don't have parameter to return other than max string

[#5]

Use

= Aggr(if((Rank([   ID  ])) =1, [   ID  ]),[   ID  ]) for Rank 1

= Aggr(if((Rank([   ID  ])) =2, [   ID  ]),[   ID  ]) for Rank 2

= Aggr(if((Rank([   ID  ])) =3, [   ID  ]),[   ID  ]) for Rank 3

[simenkg]

=FirstSortedValue(ID,-ID,2)

[simonaubert]

Hello.

Maxstring, contrary to max, does not accept the rank as second parameter. You can vote for this idea to implement it : https://community.qlik.com/t5/Ideas/Minstring-and-Maxstring-should-accept-rank-as-2nd-parameter-like...

Best regards,

Simon