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.
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?
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.
You are not mistaken, that was my mistake. I'll update the original post. Thanks for catching this!