Have you tried using previous_value()?
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.
[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
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) )
1 of 1 people found this helpful
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
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.
Previous_value is one of those hidden gems that keeps on giving...