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

[stuwannop]

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

[Anonymous]

Probably not.  I believe in mapping

[julian_rodriguez]

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 · Accepted solution]

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]

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]

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

Andy

[stuwannop]

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]

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

[stuwannop]

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]

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