Dynamic Ranking
Mike Swinn Jun 11, 2012 4:56 AMI'm working with a dataset which has a number of measures that I want to rank organisations by as a text string in the form "15th of 40" rather than simply "15". I can achieve this to a point using the basic ranking method of index() on a sorted field, combined with an intermediate calculated field 'rank_suffix':
if int(right(str([Rank]),2.0)) > 10 and int(right(str([Rank]),2.0)) <20
then 'th'
elseif int(right(str([Rank]),1.0)) == 1 then 'st'
elseif int(right(str([Rank]),1.0)) == 2 then 'nd'
elseif int(right(str([Rank]),1.0)) == 3 then 'rd'
else 'th' end
This works fairly nicely for different measures in different worksheets, as all I have to do is set the 'organisation' field to be sorted on to the specific measure (score) that I'm interested in for that worksheet.
The problem is that I have a number of measures where some scores are tied, and since index() is used it will nominally rank tied organisations differently.
I have tried the method described by James Baker in this thread which works nicely to produce standard rankings which gives ties the joint upper rank, but as this is calculated on a specific measure rather than using index(), it is not dynamic and I would have to produce three fields per measure - one for the rank, one for the suffix (-st / -nd / -rd / -th), and one for the complete string.
Ideally I would be able to create a new function which defined the suffix such as would be done in Excel with user defined functions, or acheive the same result with a dynamic calcualted field, but from what I've read these are not possible.
Can anybody suggest a way to acheive what I'm after without having to create many new variables, using the index() function or similar I would guess? I could perhaps reshape the data so the score type is in one columnd and the score in another, but that's not ideal as I'd then be combining scores of a different nature into one field.