topic Re: Rank Query in QlikView

Rank Query

Fri, 24 Oct 2014 12:11:48 GMT

Hi all,

i have below table:

Client Product pnl

1 eq 100

2 fi 50

3 com 50

4 eq 150

5 fi 500

1 fi 200

2 com 50

now i want to build a straight table with all 3 columns and add total expression as per client pnl as 4th column and rank as 5th column as rank should be on overall client as per pnl. example below

client product pnl totalpnl overall rank

1 eq 100 300 2

2 fi 50 100 4

3 com 50 50 5

4 eq 150 150 3

5 fi 500 500 1

1 fi 200 300 2

2 com 50 100 4

do let me know how to get Overall Rank column, as thats what i am not getting. rest columns i can derive

aprpeciate yoru help

thanks

Sukhwant

Re: Rank Query

sorrakis01 — Fri, 24 Oct 2014 12:15:19 GMT

Hi,

Try this:

Rank(TOTAL SUM(pnl))

Regards

Re: Rank Query

anbu1984 — Fri, 24 Oct 2014 12:22:00 GMT

Check this app

Re: Rank Query

rustyfishbones — Fri, 24 Oct 2014 12:22:10 GMT

Try

AGGR(SUM(pnl),Client) for the Total for Client

and add

AGGR(RANK(SUM(pnl)),Client) for their ranking

Re: Rank Query

Fri, 24 Oct 2014 12:26:43 GMT

no developer license

sorry

Re: Re: Rank Query

Fri, 24 Oct 2014 12:28:02 GMT

refer attached. this is what i get in F column

Re: Re: Rank Query

Fri, 24 Oct 2014 12:31:00 GMT

total column i already got and is as per expectation

your rank formula gives me '-' in all the rows

Re: Rank Query

anbu1984 — Fri, 24 Oct 2014 12:34:02 GMT

Sum(TOTAL<Client> pnl)

Rank(TOTAL Sum(TOTAL<Client> pnl),1,1)

Re: Re: Rank Query

Fri, 24 Oct 2014 12:42:04 GMT

Hi Anbu,

thanks this works for rank perfect , but now the challenge is if one selects a Product in list box like " fi" the rank column changes as per the proct selected, but i would like the rank to be shown as per the table you mentioned above

how is that possible

thanks

Re: Rank Query

Anonymous — Fri, 24 Oct 2014 14:25:24 GMT

Rank(TOTAL Sum(TOTAL<Client> pnl),1,1)

Re: Rank Query

anbu1984 — Fri, 24 Oct 2014 14:28:36 GMT

Define variable vProd=GetFieldSelections(Product)

Change expressions to

If(IsNull(vProd) Or Product=vProd,pnl)

If(IsNull(vProd) Or Product=vProd,Sum(TOTAL<Client> pnl))

If(IsNull(vProd) Or Product=vProd,Rank(TOTAL Sum(TOTAL<Client> pnl),1,1))

Create New alternate state and set that to your chart