
1. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?
Alex Kerin Nov 20, 2012 9:36 AM (in response to Nicholas Rennie)Have you tried using previous_value()?

2. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?
Nicholas Rennie Nov 20, 2012 9:39 AM (in response to Alex Kerin)I just tried the formula:
[EMA] = [alpha coefficient]*SUM([Sales]) + (1[alpha coefficient])*IF FIRST()==0 THEN SUM([Sales]) ELSE PREVIOUS_VALUE([EMA]) END
Unfortunately it still gives me a circular reference error.

3. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?
Alex Kerin Nov 20, 2012 9:51 AM (in response to Nicholas Rennie)Try
[alpha coefficient]*SUM([Sales]) + (1[alpha coefficient]) * PREVIOUS_VALUE([SUM([Sales])
The sum([sales]) inside previous_value tells Tableau what to use if there is no previous value (i.e. the first value however the partition is set up)
You also don't need the [EMA]= at the beginning

4. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?
Nicholas Rennie Nov 20, 2012 10:05 AM (in response to Alex Kerin)I didn't put the [EMA] in the actual formula, I was just using it to be clear on the circular reference.
Anyway, you did it! Thanks!!!
I'm confused, though. Why does this work? From your formula, how does PREVIOUS_VALUE(SUM([Sales])) equal the previous EMA value? Why doesn't it just equal the previous SUM[Sales] value?
Further, why does the LOOKUP function not work in this case?
[alpha coefficient]*SUM([Sales]) + (1[alpha coefficient]) * LOOKUP([SUM([Sales], 1) )
Thanks again!!!

5. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?
Alex Kerin Nov 20, 2012 10:40 AM (in response to Nicholas Rennie)1 of 1 people found this helpfulThe Sum([sales]) is only used for the first value (where there is no previous value), then it just references the result of EMA on the row above.
Lookup doesn't work because this is a moving average  we need to reference the prior EMA value, not the prior sum([sales]) value

6. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?
Nicholas Rennie Nov 20, 2012 10:46 AM (in response to Alex Kerin)Thanks Alex. It looks so simple now that I understand how the PREVIOUS_VALUE works. I just read over the Tableau documentation for this table calculation, and I understand now how it explains what you just described, but it certainly was not evident to me when I read through it before. Thanks again. I appreciate your help.

7. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?
Alex Kerin Nov 20, 2012 11:59 AM (in response to Nicholas Rennie)Previous_value is one of those hidden gems that keeps on giving...