I am working on creating a time series that shows a moving average of the underlying values (daily, in this case) I’m struggling to figure out how to obtain the previous day’s value in order to use it as the basis for calculation. (I am also running into difficulty dealing with days that don’t have values.)
I’m trying to calculate an exponentially weighted moving average (EWMA):
ewma_today = smoothing_factor*value_today + (1-smoothing_factor)*value_yesterday
I can create an index to sum multiple underlying values, and sort by date, e.g.:
day1: 1 day2: 7 day3: 2 day5: 3
My mindset to approaching the problem is to think in tables and rows, which probably isn’t helping me. It’s easy enough to build in Excel - a table. with one row per day, SUMIF for the underlying values. This blog post outlined a partial SQL approach that I found instructive.
I’d be grateful for any suggestions!