3 Replies Latest reply on Feb 18, 2017 9:51 AM by Jamieson Christian

    RMSE rollup calculations

    Mohit Shroff

      I have a table with hourly data and 1 measure. How can I create an RMSE calculation in  Tableau and roll that up from hour to daily and weekly?

       

      For eg

      1/1/2017 1PM     10

      1/1/2017 2PM     20

      1/1/2017 3PM     30

      1/2/2017 1PM     40

      1/2/2017 2PM     50

      I created an RMSE calculation for every row and it works for hourly data but when I roll up to daily, the RMSE calc doesnt hold true because it sums up averages of square root

       

      Edit: Attached workbook has forecast and actual values by hour from 1/1/2017 12AM - 1/5/2017 7PM

      I calculated RMSE as SQRT(AVG((Forecast - Actuals)^2)) for hourly data. Now if you roll up the data to daily, will the calculation for RMSE hold true?

        • 1. Re: RMSE rollup calculations
          Jamieson Christian

          Mohit,

           

          Do you have a workbook showing your progress so far? I'm rather unclear as to what exactly you're computing RMSE against. A linear regression?

           

          Please attach a packaged workbook, and we can see how your calculation(s) need to be structured to work at different levels of detail.

           

          Thanks.

          • 2. Re: RMSE rollup calculations
            Mohit Shroff

            @Jamieson I have attached a sample package workbook

            • 3. Re: RMSE rollup calculations
              Jamieson Christian

              Mohit,

               

              Thanks for sharing your workbook. The formula you are using is exactly right. The key is to write it as a simple aggregation calculation (which you can tell because Tableau notates it as AGG() when you drop it onto the view). A simple aggregation calculation will correctly compute at whatever level of detail you have on your view — hours, days, whatever.

               

              If you thought that the RMSE was not computing correctly at the DAY level, you might have made the same mistake I did — I assumed that the HOUR level of detail included only one record per hour, and I attempted to pull that output into Excel to double-check the results. When they didn't match up with what Tableau was computing at the DAY level, I got confused. Then I realized that even at the HOUR level, several rows of output are based on multiple rows of underlying data. Once I brought the raw data into Excel, then I could confirm that Tableau was correctly computing RMSE at both the HOUR and DAY level.

               

              So… yeah, in answer to your question, Tableau is correctly computing your RMSE at multiple levels of detail, and you should be good to go!

               

              Let me know if you have any additional questions.