5 Replies Latest reply on Nov 20, 2012 8:36 PM by Jonathan Drummey

    exponentially-weighted moving average (EWMA)

    David Thomas

      Under Table Calculations I'm using "Calculation Type" as "Moving Calculation" and Summarize values using "Average" going back 3 previous values.

      I notice a customize button on the bottom left that utilizes the WINDOW_AVG function like: WINDOW_AVG(SUM([Closed]), -3, 0)

       

      I'm curious if Tableau has a way to utilize EWMA (exponentially-weighted moving average) aka EMA (exponential moving average) or if perhaps someone has come up with an equivalent formula?

       

      More on EWMA/EMA here:

      http://en.wikipedia.org/wiki/EWMA#Exponential_moving_average

        • 1. Re: exponentially-weighted moving average (EWMA)
          Jonathan Drummey

          Tableau has two functions for getting values from other rows:

          - LOOKUP([field],offset) - takes an offset from the current row within the partition to find that value for [field]

          - PREVIOUS_VALUE([field]) -  takes the value of [field] in the first row of the partition, then returns the value of the calculated field PREVIOUS_VALUE is in in for other rows.

           

          I set up the Wikipedia calculation in the attached workbook.

           

          Update note 20Nov2012:

           

          In the original version of this response, I'd incorrectly used LOOKUP() instead of PREVIOUS_VALUE, and made a confusing reference to how LOOKUP() can be used. Thanks to Nicholas Rennie for catching the mistake (see the rest of the thread below). In the case of the Superstore Sales data used for this particular example, the difference between the two is small - In the attached corrected workbook I show the incorrect calc as well as the correct calc.

          • 3. Re: exponentially-weighted moving average (EWMA)
            Nicholas Rennie

            Hi Jonathan,

             

            First of all, I want to thank you for looking at this problem since I would also like to get EWMA charts in Tableau. However, I don't think your equation is correct. Your equation for EWMA Sum of Sales is effectively

            S1 = Y1

            St = a*Yt + (1-a)*Yt-1

             

            However, the Yt-1 should be St-1. Therefore, your equation looks at the previous time series value, not the previous EMA value.

             

            I've been trying to figure out how to do this in Tableau...but with no luck. Would you have the time to revisit this? Is there a way to get the St-1?

             

            Nick

            • 4. Re: exponentially-weighted moving average (EWMA)
              Nicholas Rennie

              On a different post, Alex Kearin helped me understand the difference between PREVIOUS_VALUE and LOOKUP and show me how to use PREVIOUS_VALUE for the exponentially weighted moving average calculation.

               

              Now that I am looking back over your explanations regarding both functions, you were very clear. I just didn't have the eyes to see. However, you mention above that in this specific case the LOOKUP function will work fine. Is that not the case? I think that you must use the PREVIOUS_VALUE for this to work. Please let me know if I am mistaken, since I have a lot to learn.

              • 5. Re: exponentially-weighted moving average (EWMA)
                Jonathan Drummey

                Hi Nicholas,

                 

                You are not mistaken, that was my mistake. I'll update the original post. Thanks for catching this!