topic Re: RANK (Displaying 1st, 2nd, 3rd) in QlikView

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

stuwannop — Wed, 06 May 2015 14:12:22 GMT

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

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

Anonymous — Wed, 06 May 2015 14:15:38 GMT

Probably not. I believe in mapping

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

julian_rodriguez — Wed, 06 May 2015 14:16:36 GMT

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

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

Anonymous — Wed, 06 May 2015 14:23:10 GMT

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.

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

julian_rodriguez — Wed, 06 May 2015 14:28:35 GMT

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

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

awhitfield — Wed, 06 May 2015 14:29:25 GMT

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

Andy

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

stuwannop — Wed, 06 May 2015 14:32:49 GMT

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.

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

julian_rodriguez — Wed, 06 May 2015 14:33:13 GMT

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

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

stuwannop — Wed, 06 May 2015 14:35:24 GMT

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.

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

AcumenJonFoote — Wed, 22 Aug 2018 03:37:23 GMT

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