Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: How to Rank by Lowest number first

I dont know if this helps.

Marcus

Highlighted
swuehl
Not applicable

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
Not applicable

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.

swuehl
Not applicable

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
Not applicable

Re: How to Rank by Lowest number first

Thanks, 

Negation worked for me Smiley Happy