2 Replies Latest reply on Nov 23, 2016 5:41 AM by Emma Perez

    18 month rolling calculation from two data sources

    Emma Perez

      I am trying to create a formula that takes two sets of data to create an 18 month rolling calculation. One set of data is to calculate “errors” by month, the other set of data calculates the number of “hours worked” for each month. The final 18 month rolling calculation is as follows…

       

      ((18 month rolling sum for "Errors") * 10000) / (18 month rolling sum for "work hours")

       

      Every calculation is finished, I just need to be able to take the 18 months rolling from them. Basically, I just need a formula or filter to only use 18 months worth of data for a measure named 'errors' and another measure named 'work hours.'

       

      Other information:

      The final product will be filtered by "Department," "Location," "Error type," and "Date."

      The data will be updated every month.

      The two data sources are blended by Date, Department, and Location.

       

      Any help is appreciated !

        • 1. Re: 18 month rolling calculation from two data sources
          Tom W

          What exactly do you need help with? Can you please provide some more context and upload a Tableau Packaged Workbook including sample data?

          • 2. Re: 18 month rolling calculation from two data sources
            Emma Perez

            Hello,

             

            Thank you for your reply Tom. I have actually found a simple solution to my problem.

             

            I simply created a "table calculation" for both 'errors' and 'work hours.'  For the calculation type I used "moving calculation," and for "summarize values using" I chose "sum." I then chose 'date' as a value to move along. I did 17 previous values and included the current value, giving me 18 values total to use for my calculation.

             

            After creating a separate table for both 'error's and 'workhours' I created a forumla in the shelf for my final rolling calculation, as stated above.

             

            I hope this helps someone else.

             

            Thanks,

             

            EP