7 Replies Latest reply on Nov 20, 2012 11:59 AM by Alex Kerin

    Does anyone know how to calculate exponentially weighted moving averages in Tableau?

    Nicholas Rennie

      Maybe I'm looking at this wrong, but I can't figure out how to do this in Tableau. The effective formula is

       

      S_1  = Y_1
      for t > 1,\ \    S_{t} = \alpha \cdot Y_{t} + (1-\alpha) \cdot S_{t-1}

       

      More info can be found here: http://en.wikipedia.org/wiki/EWMA#Exponential_moving_average

       

      The problem is that it is recursive. When creating a calculated field in Tableau, how does one refer to the previous value of that calculation (in the previous partition) from within the formula?

       

      For example, I have tried the formula (modified from Jonathan Drummey):

      [EMA] = [alpha coefficient]*SUM([Sales]) + (1-[alpha coefficient])*IF FIRST()==0 THEN SUM([Sales]) ELSE LOOKUP([EMA],-1) END

      but of course this contains a circular reference error.

       

      Any ideas? It seems like there must be some financial people on Tableau that need exponential weighted moving averages.