Multi calculation rolling average growth
Amy Song Jul 12, 2012 12:01 PMHi there... I am trying to put together a view of (deep breath) year over year growth of 12 month rolling average "mean time to failure."
Breaking that down... First tab of the attached workbook is failures by month, and 2nd tab is hours per month. Mean time to failure (MTTF) is defined as failures/hours per month. So the 3rd tab is essentially the 1st tab numbers divided by the 2nd tab numbers.
Quick side question here  I'm not sure if I'm calculating the hours correctly... I played around and the calculation that ended up with the right numbers is an average? (see calculated field for "Month hours")... this is sort of when I start losing understanding of what the calculations are doing.
Anyway, then the 4th tab I calculate year over year growth using the Tableau out of box table calc, which looks to be about right when I compare against manually calculated numbers (column D in attached Excel file).
What I'm trying to ultimately get at though, is y/y growth of a 12 month rolling average (see column F of the Excel). That is the objective for the last tab, but those numbers aren't close at all to the manual calculation.
I am wondering what I'm doing wrong... I think it has to do with the multi level calculations, where functions are applied and where aggregation occurs etc, but I'm not finding success in trying different options. It would be awesome if someone can help figure this out and explain to me what the underlying calculations are doing.
And a last question, is there a way to incorporate leap year values without having a separate field for that?
To summarize, here are the questions I'm seeking answers for:
1. Did I calculate hours correctly? Why does average as a function yield the correct values?
2. How do I arrive at the correct 12 month rolling average numbers? Why didn't the current set up work?
3. How can I compensate for February hours per month whether it's a normal or leap year?
Thank you!!

Book15.xlsx 9.8 KB