2 Replies Latest reply on Jul 17, 2012 1:08 PM by Amy Song

    Multi calculation rolling average growth

    Amy Song

      Hi 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!!

        • 1. Re: Multi calculation rolling average growth
          Jonathan Drummey

          Hi Amy,

           

          Here are some answers:

           

          1) MIN, MAX, AVG, and ATTR will all work in your view because they all return a single value for all the rows in the worksheet. SUM() doesn't work as an aggregation because it takes the given value and sums it up across all the rows in the worksheet, so you'd get the # of hours in the month * the number of records returned. In cases like this, I tend to use ATTR() so that way if something gets funky with the level of detail in the view, then the calculation will break.

           

          2) See the attached for details. The crosstab view shows a 12 month rolling average calc (WA Mean time to failure) that has Compute Using set to reportdate. I set up this calc to not require 12 months of data, so it reports results for 2011. Then there's a YoY WA Mean time to failure that is a nested table calc that uses the values of WA Mean time to failure. The Compute Using for YoY WA Mean time to failure is set to reportdate, with At the Level set to Year of reportdate.

           

          The results are different than your Excel spreadsheet for two reasons: the spreadsheet has data for 2010 which the Tableau workbook doesn't have, and the calc is taking the February leap year into account.

           

          3) I changed the Month hours (leap) calculation to be the following:

           

          ATTR(24*

          IF MONTH([reportdate]) != 12 THEN

              DATE(STR(YEAR([reportdate]))+"-"+STR(MONTH([reportdate])+1)+"-1")

                - DATE(STR(YEAR([reportdate]))+"-"+STR(MONTH([reportdate]))+"-1")

          ELSE

              31

          END)

           

          It's trusting Tableau's ability to add/subtract dates across months to get the right number of days. I also revised the mean time to failure calc to use this value instead.

           

          Finally, when I created the mttf 12mos line chart, I added the YoY Mean WA Mean time to failure calc to the Filter shelf and set it to filter for Non-Null values, so 2011 is excluded.

           

          Cheers,

           

          Jonathan

          • 2. Re: Multi calculation rolling average growth
            Amy Song

            Jonathan --

            Thank you so much for this lesson.  Works perfectly and this is exactly what I was hoping to learn.  Thank you!