Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

How to Rank by Lowest number first

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,

Tags (1)
7 Replies
marcuspersson
New Contributor III

Re: How to Rank by Lowest number first

I dont know if this helps.

Marcus

Highlighted
MVP
MVP

How to Rank by Lowest number first

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

Not applicable

How to Rank by Lowest number first

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

marcuspersson
New Contributor III

Re: How to Rank by Lowest number first

Hi,

can you post a .qvw file containing your problem?

Not applicable

Re: How to Rank by Lowest number first

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.

MVP
MVP

How to Rank by Lowest number first

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
New Contributor

Re: How to Rank by Lowest number first

Thanks, 

Negation worked for me 🙂