Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stuwannop
Partner - Creator III
Partner - Creator III

RANK (Displaying 1st, 2nd, 3rd)

Hi Everybody

I have created a chart and used the RANK function to display sales data with the highest first, lowest last and added a "Ranking" column. What I want to do though is instead of displaying "1", "2", "3" etc. in the Ranking column I want to display it as "1st","2nd","3rd" etc.

I expect there's something I could do with mapping but is there an easier way to achieve this?

Thanks for any help.

Stu

1 Solution

Accepted Solutions
Anonymous
Not applicable

Sure, but it doesn't look simpler than mapping to me.  What about 21st or 32nd?  You have to check not just the rank, but the last digit of the rank.  I guess, the same with mapping.

View solution in original post

9 Replies
Anonymous
Not applicable

Probably not.  I believe in mapping

julian_rodriguez
Partner - Specialist
Partner - Specialist

You may try something like this...

If(Rank(Sum(Sales)) = 1, Rank(Sum(Sales)) & 'st',

     If(Rank(Sum(Sales)) = 2, Rank(Sum(Sales)) & 'nd',

          If(Rank(Sum(Sales)) = 3, Rank(Sum(Sales)) & 'rd', Rank(Sum(Sales)) & 'th')))

Regards

Anonymous
Not applicable

Sure, but it doesn't look simpler than mapping to me.  What about 21st or 32nd?  You have to check not just the rank, but the last digit of the rank.  I guess, the same with mapping.

julian_rodriguez
Partner - Specialist
Partner - Specialist

Yeah you are right... it's more complicated than this if you need to show all the items.

I was thinking in something like a top ten chart.

May be mapping is the best solution....

awhitfield
Partner - Champion
Partner - Champion

Would this not get messy when you get equal ranks like 3-4?

Andy

stuwannop
Partner - Creator III
Partner - Creator III
Author

Yes, thanks folks - my data (oddly enough) goes up to 32nd so I'd probably rule out an IF statement in this particular case. I was kind of hoping there was some clever function that I couldn't find that did it but guess not!

Mapping it is I guess!

Thanks guys.

julian_rodriguez
Partner - Specialist
Partner - Specialist

On this case, I would try using the Rank parameters, like Rank(Sum(Sales),1,1)

stuwannop
Partner - Creator III
Partner - Creator III
Author

That's a good point Andrew, there are a lot of combinations between 1 and 32 (I don't care to do that maths).

For something that's essentially cosmetic I might not actually bother... I'm a little surprised it's not a built in function though if I'm honest.

AcumenJonFoote
Partner - Contributor II
Partner - Contributor II

This is an old post but thought I would add this as I had the same need and came up with the folowing

If($(mRank)=11,'th',

IF(Right($(mRank),1)=1,'st',

    IF(Right($(mRank),1)=2,'nd',

    IF(Right($(mRank),1)=3,'rd','th'))))

mRank calculates the rank value - - something like this     


Aggr(Rank(total Sum({1<[Set Expressions] >}count)),[AGGR LEVEL])


Cheers


Jon