topic Re: Rank function as expression in QlikView

Rank function as expression

Wed, 04 Mar 2015 11:19:37 GMT

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

Re: Rank function as expression

anbu1984 — Wed, 04 Mar 2015 11:31:29 GMT

Dim: Item

Expr: Max(ID,2)

Re: Rank function as expression

Wed, 04 Mar 2015 12:03:30 GMT

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?

Re: Rank function as expression

anbu1984 — Wed, 04 Mar 2015 12:48:03 GMT

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

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

Re: Rank function as expression

Wed, 04 Mar 2015 12:55:39 GMT

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

Re: Rank function as expression

simenkg — Wed, 04 Mar 2015 13:02:20 GMT

=FirstSortedValue(ID,-ID,2)

Re: Rank function as expression

simonaubert — Tue, 02 Feb 2021 12:03:40 GMT

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/idi-p/1779314

Best regards,

Simon