-
1. Re: Average last 10 occurances
Mark Holtz Jul 12, 2012 1:32 PM (in response to Emily Mueller)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 Jul 13, 2012 7:21 AM (in response to Mark Holtz)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
-
Packaged recipe.twbx.zip 184.7 KB
-
-
3. Re: Average last 10 occurances
Mark Holtz Jul 13, 2012 7:27 AM (in response to Emily Mueller)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 Jul 13, 2012 7:40 AM (in response to Emily Mueller)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.
-
Packaged recipe.twbx.zip 202.6 KB
-
-
5. Re: Average last 10 occurances
Emily Mueller Aug 2, 2012 1:43 PM (in response to Mark Holtz)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 Aug 3, 2012 7:15 AM (in response to Emily Mueller)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!
-
Packaged recipe.twbx.zip 328.2 KB
-
7. Re: Average last 10 occurances
Emily Mueller Aug 6, 2012 10:00 AM (in response to Mark Holtz)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