Rank challenge

building reports webintelligence
1

Hi All,

I have a strange request from the client. This is how I currently have.

Rank 2009 2008
1 20 18
2 16 19
3 14 21
4 11 12
5 9 16
0 15
0 17

When we have 0 value in 2009 then it does not show Rank which is correct.
Now client has requested that he does not want to have blank cell in the Rank column. The report should first apply Rank on year 2009, and when you encounter 0, then it should check the corresponding values in 2008 (15 and 17 here) and the apply the rank on 2008 and get the output. The required output is as below.

Rank 2009 2008
1 20 18
2 16 19
3 14 21
4 11 12
5 9 16
6 0 17
7 0 15

Appreciate any help in this regards. Thanks

yogendra (BOB member since 2004-10-19)

2

Hi,

Would it be possible to concatenate the numbers (as strings) then convert back to a number and rank just on that number?

So you would get:

Rank 2009 2008 Concatenated
 1    20    18    2018
 2    16    19    1619
 3    14    21    1421
 4    11    12    1112
 5     9    16     916
 6     0    17      17
 7     0    15      15

Pete

Peter Hughes :uk: (BOB member since 2005-11-21)