Rank Query

[#1]

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

[sorrakis01]

Hi,

Try this:

Rank(TOTAL SUM(pnl))

Regards

[anbu1984]

Check this app

[rustyfishbones]

Try

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

and add

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

[#5]

no developer license

sorry

[#6]

refer attached. this is what i get in F column

[#7]

total column i already got and is as per expectation

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

[anbu1984 · Accepted solution]

Sum(TOTAL<Client> pnl)

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

[#9]

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

[Anonymous]

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