How to Rank by Lowest number first

[#1]

Hi, I am just at my wits end on this...  I am using a calculated dimension to give a numerical rank to Hospitals based on a score.   The problem is that the Rank formula seems to only work in decending order so the highest score is ranked 1... what i need is the reverse the lowest score should be ranked 1.

Here is my expression in the calculated dimension

=aggr(rank(Total Aggr((sum(Score)), hospital),0,1), hospital)

Any help would be much appreciated.

by the way i have tried the following but it still won't work

=aggr(rank(Total Aggr(((sum(Score) * -1)), hospital),0,1), hospital)

thanks,

[Anonymous]

I dont know if this helps.

Marcus

[swuehl]

I also believe that negating the sum(Score) should do what you want (like in Marcus example).

I also think that you don't need the inner aggr() function, probably

=aggr( rank(-sum(Score) ,0,1), hospital)

should be enough.

Regards,

Stefan

[#4]

well i think i am getting closer... but now the problem is that the rankings seem to start at 28 rather then at 1 but the order is right

[Anonymous]

Hi,

can you post a .qvw file containing your problem?

[#6]

Ok here is a QVW file..   So as you can see the calculated dimention works with you choose "Decubitus Ulcer" and ranks the hospitals 1,2,3,4 but when you choose " Post-op Sepsis"  the ranking starts at 2 rather then 1.

[swuehl]

Rank 1 is assigned to Hosp A, but the expression value avg(raj) is zero. Since you suppress zero values in presentation tab, this line is hidden.

[anu]

Thanks, 

Negation worked for me 🙂