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)
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.
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.