Skip to Content
0
Former Member
Jan 10, 2012 at 06:48 AM

Calculating Rank in Query both Upwards & Downwards

246 Views

Hi,

I have a Bex Query in which Collection & Outstanding (Closing Balance) are calculated. I need to rank the Sales Officers based on this both. For Collections, the largest value should be Ranked 1 & the lowest should be the last Rank. But for Outstanding, the lowest should be Ranked 1 & the largest as Last Rank. For Ex, consider the below case:

Sales Officer Collection Outstanding

A 25000 13000

B 12000 8000

C 18000 5000

D 33000 20000

E 10000 40000

The Output should be as follows.

Sales Officer Collection Outstanding Collection Rank Outstanding Rank

A 25000 13000 2 3

B 12000 8000 4 2

C 18000 5000 3 1

D 33000 20000 1 4

E 10000 40000 5 5

I used the Rank number option in Bex Query (In Calculations tab) but it calculates Rank only for Collection, that is largest to smallest value. I couldnt sort out how to calculate Rank for Outstanding, as it is from lowest to Highest.

Any help on this is highly appreciated.

Thanks & Regards,

Murali