2 Replies Latest reply on May 18, 2013 8:49 PM by Richard Leeke

    Coloring by Quintile Calculated Field

    ryan.sleeper

      I would like to add historical context to performance reports by coloring measures by quintile.

       

      For example, if conversions during February were in the top 20%, they would be colored green. If conversions in December 2011 were in the 60-80th percentile, they would be colored a lighter green. The 40-60th percentile receives blue, and so on.

       

      I currently do this by using a combination of the INDEX() rank calculated field workaround and the following formula in a separate calculated field:

       

      IF (INDEX()/SIZE()) > .8 then "5th Quintile"

      ELSEIF (INDEX()/SIZE()) > .6 then "4th Quintile"

      ELSEIF (INDEX()/SIZE()) > .4 then "3rd Quintile"

      ELSEIF (INDEX()/SIZE()) > .2 then "2nd Quintile"

      ELSE "1st Quintile" end

       

      This approach has two major limitations:

       

      1. The data has to be sorted by performance in order for each measure to receive the appropriate color. If it is sorted by month, the ranking will be chronological.

      2. If I place a filter for a particular month, the SIZE() is only one, and thus, the measure will always be in the top quintile.

       

      Does anybody know a better way to do this? I would like to be able to filter to look at a specific month's performance, but have it maintain the historical comparison to all data points in the file.

       

      I have attached a practice example.

        • 2. Re: Coloring by Quintile Calculated Field
          Richard Leeke

          You should be able to do this with a couple of minor changes to what you have in the example. I've attached a workbook with a very quick go at this - but confess to not having checked the answers, so please look carefully. I'm pretty sure that at least the principle is right, though, if not the detail. ;-)

           

          The two changes address your two issues:

           

          1) The sort order. If you define explicitly the sort order that you want for the Quintile calculation, you can have the view sorted by whatever you want. If you click on the Quintile pill on the colour shelf, select Edit Table Calculation and then go into the advanced option, you can specify that you want the calculation to be sorted by SUM(conversions). That is then independent of the display order.

           

          2) If you want to filter the view without impacting the rankings, you need to define a calculated field which can be used to filter after table calculations have been applied. If you look in the attached workbook you will see that I've defined a field [Foilter Date] as defined below. This is a trick, to allow the filter to be applied after the table calculation.

           

          [Filter Date] => LOOKUP([ATTR([Date]), 0)

           

          The LOOKUP function is a table calculation. With an offset of 0 it just means lookup the current value of the expression - but as it is a table calculation, Tableau defers evaluation of any filters till after all table calculations have been evaluated.

           

          Hope that makes sense.