5 Replies Latest reply on Jun 21, 2012 2:35 AM by Mike Swinn

    Dynamic Ranking

    Mike Swinn
    Mike Swinn Jun 11, 2012 4:56 AM

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

      • 1500 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Dynamic Ranking
          Mike Swinn
          Mike Swinn Jun 18, 2012 6:45 AM (in response to Mike Swinn)

          I've created an example workbook to make the problem clearer. I have five companies with three measures - sales, profit and employees. Each of the measures has some repeated values and therefore the simplistic ranking with index() is not suitable as it separates identical companies.

           

          The reason this setup is ideal though is that I can create the 'rank suffix' and 'rank (with suffix)' fields completely indepedently of the measure I'm using, then simply duiplicate the view, drag the measure I want onto the measure values shelf and change the company dimension to sort by that particular measure.

           

          To keep it simple for many measures, I need a way of performing a smart ranking without ever referring directly to one of the measures in any calculation. Index() acheives this nicely but does not support tied rankings. James Baker's method linked to in my intial post works very nicely for a single field, but is not dynamic as it requires a specific variable name in the calculation, therefore I'd need to add new fields per variable.

           

          I therefore need a way to directly or indirectly bring the measure into the calculation - a dynamic variable reference or equvalent.

           

          This may not be possible, but I want to avoid reshaping the data into one column, or creating many duplicate calculations.

          • 2. Re: Dynamic Ranking
            Jonathan Drummey
            Jonathan Drummey Ambassador Jun 18, 2012 9:27 PM (in response to Mike Swinn)

            Hi Mike,

             

            Here's one option. It uses a parameter to set the measure of interest, then two calculated fields that use the parameter - one to generate the proper sort for each measure, the other to return the value of the measure. Then the Standard Rank, Standard Rank Suffix, and Standard Rank (with suffix) calculated fields all refer to the measure.

             

            With the parameter in the view, you can choose the parameter and the view updates for the measure.

             

            Hope this helps!

             

            Jonathan

            1 of 1 people found this helpful
            • 3. Re: Dynamic Ranking
              Mike Swinn
              Mike Swinn Jun 19, 2012 2:11 AM (in response to Jonathan Drummey)

              Thanks Jonathan, that does work nicely for a view, but I'm wondering if it's possible to use a single method for multple views so all measures can be shown simultaneously on one dashboard (I should have explained this better).

               

              If there is some way to make parameters local, or perahps create three parameters (one per variable) and refer to the locally used one dynamically, this could be acheived. At the moment though I'd need to duplicate the 'Measure' and 'Measure for sort' variables as well as the parameter.

               

              One alternative I've thought of could be a column based 'offset' type function which would for example refer to the value in column (measure) to the left in a view without there being any defined relationship between the measures. This would obviously be quite risky though as any change in the layout would result in a meaningless output, and I don't beleive such functions exist for this reason.

               

              Perhaps a rank() function in the next version is the only simple solution?

              • 4. Re: Dynamic Ranking
                Jonathan Drummey
                Jonathan Drummey Ambassador Jun 19, 2012 9:42 AM (in response to Mike Swinn)

                Hi Mike,

                 

                I'm having a hard time coming up with any solution to meet your need that doesn't involve creating all the calculations.

                Tableau doesn't give us access to the worksheet name (which would be one way to vary the calculation), nor when using Measure Names/Measure Values does it let us know which Measure Name/Value is being populated, or reference other Measure Names/Values. The LOOKUP() function can do all sorts of magic, but it's a table calculation and the results of table calculations can't really be used for addressing or partitioning of other table calculations except through serious gymnastics.

                 

                I think you're going to have to bite the bullet and create the calculations for each measure. You could reduce it to two calculations per measure, one for the standard rank and then the other one for the combined rank & suffix. This is a case where I will typically copy the formulas into a word processor and use copy, search & replace to make a bunch of calcs at once that I can copy & paste into Tableau.

                 

                You haven't said anything specific about how many measures you have, or your data volumes. If you are getting into dozens of measures, and/or more than 10,000s of thousands of rows, you may run into some performance issues since you'd be asking Tableau to dynamically perform millions of calculations to create the view and might want to think about doing the ranking in the data source, if that's possible.

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Dynamic Ranking
                  Mike Swinn
                  Mike Swinn Jun 21, 2012 2:35 AM (in response to Jonathan Drummey)

                  Yes that sounds right, it's no big deal in this case - I have 5 measures for what I'm working on currently and I created the ranking and output fields in Excel which was faster. That said it would be very nice if Tableau could add some more dynamic capacbilities so we don't have to do any intermediate processing.

                   

                  I like your suggestion about word processing to manipulate functions though, this will definately save me time in some cases and it make the overall time comparable to using Excel intermediate in a situation such as this.