Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Rank function as expression

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

5 Replies
anbu1984
Honored Contributor III

Re: Rank function as expression

Dim: Item

Expr: Max(ID,2)

Not applicable

Re: Rank function as expression

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
Honored Contributor III

Re: Rank function as expression

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

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

Not applicable

Re: Rank function as expression

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

Partner
Partner

Re: Rank function as expression

=FirstSortedValue(ID,-ID,2)