CODEWITHSUNDEEP
activepivotatoti
Alex
Alex

Reputation: 1

How to build a simple moving average measure

I want to build a measure to get the simple moving average for each day. I have a cube with a single table containing stock market information.

Schema
Schema

The expected result is that for each date, this measure shows the closing price average of the X previous days to that date.

For example, for the date 2013-02-28 and for X = 5 days, this measure would show the average closing price for the days 2013-02-28, 2013-02-27, 2013-02-26, 2013-02-25, 2013-02-22. The closing values of those days are summed, and then divided by 5.
The same would be done for each of the rows.

Example dashboard
Example dashboard

Maybe it could be achieved just with the function tt..agg.mean() but indicating those X previous days in the scope parameter.

The problem is that I am not sure how to obtain the X previous days for each of the dates dynamically so that I can use them in a measure.

Upvotes: 0

Views: 167

Answers (1)

Daniel Taylor
Daniel Taylor

Reputation: 1

You can compute a sliding average you can use the cumulative scope as referenced in the atoti documentation https://docs.atoti.io/latest/lib/atoti.scope.html#atoti.scope.cumulative.

By passing a tuple containing the date range, in your case ("-5D", None) you will be able to calculate a sliding average over the past 5 days for each date in your data.

The resulting Python code would be:

import atoti as tt
// session setup
...
m, l = cube.measures, cube.levels
// measure setup
...
tt.agg.mean(m["ClosingPrice"], scope=tt.scope.cumulative(l["date"], window=("-5D", None)))

Upvotes: 0

Related Questions