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.