7 Replies Latest reply on Aug 6, 2012 10:00 AM by Emily Mueller

    Average last 10 occurances

    Emily Mueller

      This seems like it is simple, but I can't figure out how to do it.

       

      I have dates, different recipes run on different dates, and results for those recipe runs.  I want to average the results for the last 10 runs of each recipe.  I have tried to filter on count of date, but it doesn't seem to be working.

       

      Any help or advice would be appreciated!

       

      Emily

        • 1. Re: Average last 10 occurances
          Mark Holtz

          I believe you're looking for the WINDOW_AVG function.

           

          It will calculate the average of datapoints over the window you specify

           

          So in your case, if you measure is "Results", and you've got the date you want as your dimension on the row or column shelf, you'd probably have something like:

          WINDOW_AVG([Results],-9,0)

          (whenever you use 0 as the start or endpoint in any of the WINDOW_### functions in Tableau, you will subtract 1 from the desired window size--hence -9 instead of -10.)

           

          If it wasn't obvious, you'd create a new calculated field where you'd use this function.

          • 2. Re: Average last 10 occurances
            Emily Mueller

            Mark- Thanks for the response, however I'm still not getting an accurate result.  I have attached a packaged workbook for you to take a look.  I'm guessing I don't have my table calculation set up correctly, but I can never seem to figure those out.  Can you take a look and help me out?

             

            Thanks!

             

            Emily

            • 3. Re: Average last 10 occurances
              Mark Holtz

              Hi Emily,

               

              Close.  I forgot to mention that with WINDOW_### functions, you have to specifiy an aggregation within them.

              Usually, you want to use SUM because you are looking to get the moving average of the total.

               

              Try this:

              WINDOW_AVG(SUM([Weight per vat]),-9,0)

               

              Does that get it?

              • 4. Re: Average last 10 occurances
                Mark Holtz

                Also, here's your workbook.

                 

                I will also mention a 2nd way to do the same thing.  It's perhaps simpler:

                Observe the new "Sheet 2" in the attached workbook.

                If you hold Ctrl and drag the "Sum(Weight per vat)" green Measure pill next to itself on the Rows shelf, Tableau will place a duplicate Measure for you. Then right click the duplicate pill and "Add Table Calculation."

                 

                You can choose the Calculation Type = "Moving Calculation"

                for the Summarize values using: , choose "Average"

                Then for Previous Values, you'd use 9 and next values 0 (check Include current value).

                 

                Since Moving Average is fairly common, Tableau tried to make it easy--this method will accomplish the same thing as creating the separate calculated field.

                • 5. Re: Average last 10 occurances
                  Emily Mueller

                  I still couldn't get this to work, and so I thought I would take a break from it for a while.  However after looking at this again today, I am still not getting an accurate result.  If I view the data and average the last 10 occurances, it gives me a completely different number than it is showing.

                  Does it make a difference if I only need to see what the current average of the last 10 results is? 

                   

                  I know I must be missing something and it's frustrating that I can't figure it out.

                   

                  Any help is appreciated!

                   

                  Thanks!

                   

                  Emily

                  • 6. Re: Average last 10 occurances
                    Mark Holtz

                    Hi Emily,

                     

                    I re-read your original post, and I think I missed a detail. You wanted average results for each recipe.

                    That is not what I understood originally.

                     

                    You said you want to average the last 10 runs, but am I correct that you do not care how many days ago the runs were, you always want to calculate the previous 10?

                     

                    For example, the last 10 runs for HTST Recipe #10 were 6/27, 5/30, 5/9, 5/9, 4/18, 4/18, 3/21, 2/17, 1/18, and 12/28.

                     

                    The problem with that is that there is no way in the underlying data to distinguish the 1st 5/9 run from the 2nd for Recipe #10. I don't know if it's possible within Tableau, but you could certainly just add one additional primary key column to the underlying data to allow additional differentiation. (so, just a "runID" that is an ascending integer, 1-2641 or more...)

                     

                    Then, with an index on this "RunID" field, you will just want to average the results values for the 10 "greatest" ID's for a given recipe.  I have attached a workbook with the modified workbook and also with a new copy of your data with the field I'm talking about added.

                     

                    Hope that's what you're after!

                    • 7. Re: Average last 10 occurances
                      Emily Mueller

                      Mark-

                       

                      Thank you so much for the time you put into this.  This works great, and is exactly what I was looking for.  I did notice when I added my moving average table calculation, I did need it to move along Table down.  Moving along RunID didn't seem to produce the correct results, but moving along Table down worked perfectly.

                       

                      Again, thanks for taking the time to help me out with this!

                       

                      Emily