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?

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}$

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.

• ###### 1. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?

Have you tried using previous_value()?

• ###### 2. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?

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?

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?

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?

The 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

1 of 1 people found this helpful
• ###### 6. Re: Does anyone know how to calculate exponentially weighted moving averages in Tableau?

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?

Previous_value is one of those hidden gems that keeps on giving...