Rolling average, exponentially weighted moving average

Help
1

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!

2

Quickly looking at that example, what they are doing seems to be a self join based on an interval. I’m not entirely sure without coding it myself but I would think you can accomplish this by:

  • Start with the index/collection results
  • Calculate the lower date (-6 days in example) with Fauna’s date functions.
  • Map over the Paginated results there and use a Range on an index (e.g. Range(Match(Index(“index_by_date”))) to retrieve the documents that are within 6 days.
  • Write a number of nested If tests for the Case.

Does that make sense?
The major difference here is the Map here, I assume you are thinking in ‘joins’, think more in a programmatic or graph-traversal like way. There is a limit to the page size once you use Map though which you can increase up to 100k. If that’s not enough you need to do it in multiple calls.

3

Thank you, Brecht. I was able to make progress getting the underlying data into good shape, thanks I think to some of your StackOverflow posts. :slight_smile: Using a couple of indexes, map & reduce, etc.

If I have data that looks like this:

  • day1 = 1
  • day2 = 7
  • day3 = 2
  • day5 = 3

I’d like to calculate something like a cumulative total (not quite, but for simplicity’s sake):

  • day1 = 1
  • day2 = 1+7 = 8
  • day3 = 8+2 = 10
  • day4 = 10+0 = 10 (some special handling needed here)
  • day5 = 10+3 = 13

Following your notes above and the documentation on Map(), I think I could Map() over each date and also retrieve prior days’ documents, so presumably I could store & retrieve each of those results at each step along the way. Instead, does the Map(Lambda()) have access to the results of the previous Lambda call?

4

Hi @reed! This is a really interesting problem. Were you able to work it out?

Recursive UDFs are possible, but limited to to 200 calls.