1 Reply Latest reply on Oct 24, 2016 3:13 PM by Patrick A Van Der Hyde

    AVG Daily Running Total For the Month

    Kip Goldhammer

      I am trying to figure out how we could possibly get the AVG Daily Running Total for each month.  I have looked and search everywhere I can for an answer for this and have not been able to figure it out.  I tried using WINDOW_AVG(RUNNING_SUM(sum([Total Ready Feeder]))) .... maybe I don't know how to use it correctly?

       

      In the above example I want to get the average of the daily running total.

       

      The calculations:

       

      Total Ready Feeder: = 

      CASE [Start-End]

      WHEN 'Start' THEN  1

      WHEN 'End'   THEN -1

      END

       

      Total Ready = RUNNING_SUM(sum([Total Ready Feeder]))

       

      AVG Total Ready = WINDOW_AVG(RUNNING_SUM(sum([Total Ready Feeder])))

       

      This is how I am trying to set the partitioning:

      But no matter what I do I can't seem to get the AVG of the daily running total for the month.

       

      You can see in the screenshot at the top Tableau is giving me negative numbers for the avg when there are no negative numbers for the daily running total.  I guess it is averaging the sum of the feeder calculation instead of the running_sum... but I'm not sure.

       

      Any ideas on how to do what I'm looking for?

        • 1. Re: AVG Daily Running Total For the Month
          Patrick A Van Der Hyde

          Hello Kip,

           

          I have attached two examples of avg of a running sum.  The left column uses Pane Down and provides a Avg of the Running Sum for just the values of the current month (starting at zero for each month).  The right column is the avg of the Running Sum over all time with the Avg being just for the values consisting of the current month.

           

          This is in version 10 of Tableau Desktop.  I didn't know what version you were working with but it could be done in any other version as well.

           

          Left window_avg is just Compute using Pane down

           

          Right  window_avg is using the dimensions Month of Order Date (first) and Day of Order Date second.

          Set the At the Level fo "Day of Order Date" - since we are computing the avg per day and then set the Restarting every to "Month of Order Date". 

           

           

           

          I hope this helps out. 

           

          Patrick