8 Replies Latest reply on Feb 16, 2012 7:02 PM by Shawn Wallwork

    moving average of running total table calculation

    Zach Leber

      I am using the technique described in this thread to plot a count of running tasks, in this case using the sales data to count open orders. Is it possible to add a moving average to my running total table calculation?

      Running Total.jpg

        • 1. Re: moving average of running total table calculation
          Shawn Wallwork

          Zach, here you go (see attached).

           

          What I did was create a dual axis chart, and on the second axis I wrapped your running count formula in a moving average formula. Wasn't sure how smoothed you wanted it. This does 30 days before and 30 days after. The /60 days brings it back to a daily average. One note: for some reason Tableau grayed out the sync axis choice so I had to fix both of them the same. Hope this is what you were looking for.

           

          Zach-5.PNG

           

          To get this I checked the null option:

          Zach-3.PNG

          And then hide special values:

           

           

          Zach-4.PNG

           

          Message was edited by: Shawn Wallwork

          1 of 1 people found this helpful
          • 2. Re: moving average of running total table calculation
            Zach Leber

            Thanks Shawn, that works well.  I'm still learning my second level table calculations.  I switched from WINDOW_SUM / [end-start] to the simpler WINDOW_AVG.  But I discovered that because we're plotting discrete dates even if on a continuous axis, the units of this window calculation are not elapsed days but rather date events, i.e. a start and stop for each event, so -30 is actually only a 15-event moving average.  I don't think there's a solution to that so I set the window to twice our recent monthly event count and parameterized the value to give the user control.

             

            The reason the axes won't sync is because Tableau requires that the dual measures have the same number type so I wrapped them both in INT. 

             

            Here are my latest formulas and my actual data.  Note that my moving average isn't null at the beginning of the displayed data range is because there's lots of data before what's shown that I'm excluding using a table calculation filter described here.

             

            // Running Total

            INT(RUNNING_SUM(SUM([Delta])))

             

            // Moving Average

            INT(WINDOW_AVG(RUNNING_SUM(SUM([Delta])),-[Moving Average Window],0))

             

            Moving Average.png

            1 of 1 people found this helpful
            • 3. Re: moving average of running total table calculation
              Shawn Wallwork

              Zach thanks for the feedback on how it worked and the modifications you needed to make to get it to work for you. And the education about the axes format issue. Not sure I deserve the green star, but I am happy my post lead you in the right direction.

               

              --Shawn

              • 4. Re: moving average of running total table calculation
                Zach Leber

                In my reply above I am using [Moving Average Window] to determine how many records to average.  We really want this to be days, but the window calculation operates on records, so I created a user parameter which is set to a typical value representing the number of records seen for the desired window width in days.  With my data, this was approximately 300 records for 30 days.  But I figured out how to compute this dynamically instead.  It's not perfect, but it's pretty good, and even responds to changing rates over time.

                 

                First I created a new parameter called [Moving Average Days] where the user can enter the number of days.  Then I changed [Moving Average Window] from a parameter to a calculated field that is calculated per record.

                 

                // Moving Average Window

                INT([Moving Average Days]/[Days per record])

                 

                I calculate [Days per record] by figuring out how many days have elapsed for the previous X records and dividing by X.  X can be anything but should be large enough to smooth out daily variations but not so large as to be insensitive to long-term rate changes.  I choose an empirical value based on the average number of records for the default window width I want (300 records in 30 days).

                 

                // Days per record

                (ATTR([Date])-LOOKUP(ATTR([Date]),-300))/300

                1 of 1 people found this helpful
                • 5. Re: moving average of running total table calculation
                  Zach Leber

                  Okay Shawn, I took away your green star, only to see if anyone else has any more ideas.  You've got plenty of points anyway.  Your response was all I needed to figure my problem out, and the thoroughness of your post is much appreciated. 

                   

                  Now that I think about it, I have this same problem in another important workbook, which plots event yields at discrete times over a continuous date axis.  I want to show a 30-day moving average there too but instead I have to show a 10-event moving average.  This seems like a general and important problem without a clean Tableau solution.

                   

                  My current hack is pretty good though if you follow it all through.  Though I'm sure it could be improved.

                  • 6. Re: moving average of running total table calculation
                    Shawn Wallwork

                    Zach if you'll post your updated workbook (after you adjusted for records per day) I'd like to mess with it bit. Also you might want to post your question again in another thread, the 'assumed answered' genie tag your post so you probably won't get much more play on this one.

                     

                    Thanks,

                     

                    --Shawn

                    • 7. Re: moving average of running total table calculation
                      Zach Leber

                      Breakthrough.  After I modified Shawn's version of the sales workbook with my forumlas, I found the results were almost the same.  Then I realized that's because there are order events for every day, and the date format is set to date, which means the columns of the table are exactly days, so the window calculation units are days.  Whereas in my data, my events are plotted in datetime rather than date, which means there is not a 1:1 relationship between columns and days, hence the need for all my formulas.  When I switched my data from datetime to date, everything got smoother and I had events for almost every day so my units are almost 1:1.  My formulas are still required for when there are days with no data, though if this is rare, and you're only concerned with window averages, the effect is minimal.

                       

                      Here's what my data looks like now, compare to the first picture I posted above.  I think for this graph users will prefer the smoothing.

                      Moving Average.png

                      Unfortunately this doesn't work as well for my other workbook which is a dense scatter plot of yields over time, which uses datetime instead of date as a way of jittering the data to see more when there are multiple events per day.

                       

                      I've attached the sales workbook with a revised and simplified version of my formulas so you can see what I'm doing though it's hard to see the impact because the date domain is complete.  I suppose you could filter the data to remove dates to simulate the impact of sparse data.

                       

                      Thanks for all your help Shawn, I've learned lots.  It was I who marked this thread Assumed Answered.  I wish I could give you the green star back, your solution worked perfectly for the sample sales data I provided.

                      • 8. Re: moving average of running total table calculation
                        Shawn Wallwork

                        Congrats Zach! When I was reading about your records per day I was wondering why they weren't just aggregating by the day, which is why I wanted to take a look. It's good to know the different effect datetime and date have on charts. Thanks for share, and you should most definitely give yourself the green star, I just watched.

                         

                        --Shawn