4 Replies Latest reply on Dec 13, 2013 10:26 AM by Michael Nealey

    Rank a moving average

    Michael Nealey

      OK - so I have a (moving average) table calculation and I'd like to show a "bump" chart, with the region's correct ranking for the table calculation.

      As you can see in sheet "Aggregate", that is easy to do with a simple aggregate, since I can use the calculation "Rank" and sort by the Sum of Sales...

      But with the sheet "Table Calculation", I cannot sort by my table calculation...

        • 1. Re: Rank a moving average
          Andrew Ball

          Hi Michael,

           

          I've attached a workbook with the solution in.

           

          The reason why you were having problems is that the INDEX() function as a table calculation doesn't explicitly happen after the table calculation giving you the moving average.

          Thanks to new functionality, you can do what you are looking for with the RANK() function, which allows you to tell tableau what you want to use for the rank.

          To make this work I've added two new fields:

          1) Added the moving average as a calculated field, so it can be referenced in..

          2) Added a RANK function that uses the first calculation.

           

          Yes, these two could be combined into one, but I've left them separate for clarity.

           

          Hope this helps.

          • 2. Re: Rank a moving average
            Michael Nealey

            Andrew Ball - thank you!

            Now - since we're not yet using 8.1 in our Server environment, how might we achieve this in 8.0?

            Sorry, I should probably have used an 8.0 workbook to demonstrate my question...

            • 3. Re: Rank a moving average
              Andrew Ball

              Ah.

              Erm, upgrade to 8.1?

               

               

              I can't actually think of how you could do this in 8.0 in a easy way whilst staying purely within the view designer. The problem is that you can't tell the table calculation to order by another table calculation, or by data from another source. This means you need to calculated the moving_averages prior to Tableau.

              It's not actually too hard to do in this case, but if you need a hand, let me know.

               

              Hint: 2 self joins with an date field incremented by 1 and 2 quarters respectively..

              • 4. Re: Rank a moving average
                Michael Nealey

                Thanks Andrew - working on getting our Systems folks to upgrade sooner rather than later!