4 Replies Latest reply on Aug 18, 2015 2:28 PM by Esther Aller

    Showing maximum and minimum with a calculated moving average

    Clare Elliott

      Hi

      I'm new to Tableau and would really appreciate if someone could just help me with a query I have about how to do something! It's probably really simple but I'm really stuck and so any pointers would be great please.

       

      I have a dataset which gives the number of laboratory confirmed organisms per week from 2011 until now. Ideally I would like to show this data as a chart with the 3 week moving average (1 before-current-1after) of the number of notifications for this year by week overlaid on the 3 week rolling average per week for the combined data for the previous 4 years.

       

      So far, so good - I have managed to do this by calculating the weekly average for 2011-2014 using SUM([Number of Records])/COUNTD([Year No]) and then doing a quick table calculation (moving average) on this - so in effect I have an average of the average. This all seems ok and the numbers correspond to what I calculate using excel etc.

       

      However, this is the bit that's stumping me! I also want to show the maximum and minimum range of my calculated average....so to illustrate with some data:

       

      Header 12011201220132014
      week 666518242
      week 759587350
      week 851666364
      Average 58.658.372.652

       

      Therefore from the above:

      For week 7 - the three week rolling average is 58.6+58.3+72.6+52/4=60.4 (and this is what Tableau gives me...whoopee)

      However, I would also like to display the minimum of the averages (52) and the maximum (72.6) on either side of the moving average line (for all the weeks obviously). I thought that using the quick calculation-moving calculation-minimum (or maximum) might be the solution but the numbers are wrong (and that would be far too easy!).

       

      So please, if anyone could help me do this I would be very, very grateful!

       

      Many thanks and here's hoping

       

      Clare

        • 1. Re: Showing maximum and minimum with a calculated moving average
          pooja.gandhi

          Hi Clare,

           

          Welcome to the forums! Maybe you can set up your view to something like below? And set the labels to display min/max of your calculated field that will be on the view instead of CNT(Quantity) in my example? So for 2011, min value is 9 and max is 54.

           

          You can place the weeks and your measure in your view and have the years on color on the marks card.

           

          Capture.PNG

          • 2. Re: Showing maximum and minimum with a calculated moving average
            Clare Elliott

            Hi Pooja

             

            Many thanks for taking time out for help to answer my query. However, I don't think that is really the solution I'm looking for. I probably didn't explain what I want to do very well in the first place!

             

            What I want to do is produce a chart that looks something like the one below:

            Tableau query.jpg

            So I want to aggregate the previous 4 years worth of data (and not show as individual years), average it for each week and then display it as a 3 week rolling average (which I've done) and also calculate and display the maximum rolling average  and the minimum one too. That way it can be easily seen if the rolling average for this current year falls within the expected range as calculated from the previous 4 years worth of data.

             

            Not sure if I've made this any clearer but any help on how to do this would be amazing!

             

            Many thanks

             

            Clare

            • 3. Re: Showing maximum and minimum with a calculated moving average
              Clare Elliott

              BUMP - can anyone help please??

              Thank you

              • 4. Re: Showing maximum and minimum with a calculated moving average
                Esther Aller

                Hi Clare,

                 

                I am the Tableau Technical Support Specialist that assisted with this issue back in July. I saw from the survey that the provided solution worked, which is great! I thought I would go ahead and share the solution on the forum in case anyone else has the same issue.

                 

                CREATE THE CALCULATIONS

                1. Create a calculated field named "2015 Sales" with a formula similar to the following:

                  IF DATEPART( 'year', [Order Date] ) = 2015
                  THEN [Sales]
                  END

                2. Create a calculated field named "Previous Sales" with a formula similar to the following:

                  IF DATEPART( 'year', [Order Date] ) != 2015
                  THEN [Sales]
                  END

                3. Create a calculated field named "Moving Avg" with a formula similar to the following:

                  WINDOW_AVG( SUM( [!Previous Sales] ), -1, 1 )

                4. Create a calculated field named "Min of Moving Avg" with a formula similar to the following:

                  WINDOW_MIN( [!Moving Avg] )

                5. Create a calculated field named "Max of Moving Avg" with a formula similar to the following:

                  WINDOW_MAX( [!Moving Avg] )

                6. Create a calculated field named "Avg of Moving Avg" with a formula similar to the following:

                  WINDOW_AVG( [!Moving Avg] )

                 

                BUILD THE VIEW

                1. Drag [Order Date] to the Columns shelf
                2. Right-click on [Order Date] on the Columns shelf and select More > Week Number
                3. Drag another copy of [Order Date] from the data pane to Detail on the marks card
                4. Drag [2015 Sales] to the Rows shelf
                5. Right-click on [2015 Sales] and select Quick Table Calculation > Moving Average
                6. Right-click on [2015 Sales] again and select Edit Table Calculation
                7. In the Table Calculation dialog, set Previous Values and Next Values each to 1 and click OK
                8. Drag [Min of Moving Avg] over the y-axis until the cursor shows two green bars and drop it
                9. Right-click on [Min of Moving Avg] and select Edit Table Calculation
                10. In the Table Calculation dialog choose Advanced for Compute using
                11. In the Advance dialog move Year of Order date over into the Addressing pane (on the right) and click OK
                12. Repeat steps 8-11 for [Max of Moving Avg] and [Avg of Moving Avg]
                1 of 1 people found this helpful