Moving Average Column

1

Hello,

I have time series data (published analytics) for which I’d like to add a column of the moving average with a formula. For example:

Date, Views, Sessions

I’d like to create charts that show a 7 day average of view/sessions, so to do so i was going to add a column that is an average of the last week 7 days of views.

Struggling to figure out what the formula for this column should be.

Any thoughts?

Update:

I think this is the formula:

thisTable.Filter(Date>= thisRow.Date-Days(7)).Views.Average()

2

hi @Nick_Short

what about something like below. This gives you the dates from today 7 days back. Next you can filter your data on these dates and ask for the views.

Sequence(Today(),Today() -7)

By adding a bit extra you convert the numbers into dates if that feels better for you:

image
image1531×378 38.4 KB

enjoy, Christiaan

3

Thanks christian but this is sadly not working for me:

thisTable.filter(Date = sequence(thisRow.Date,thisRow.Date-7).formulaMap(todate(CurrentValue))).Views.Average()

4

I see your point and the below might be of help:

image
image1888×548 61.4 KB

image
image1988×1705 269 KB 


thisTable.Find(thisRow).WithName(RowIndex, 

Sequence(1,RowIndex).FormulaMap(thisTable.thedate.Nth(CurrentValue))).Slice(-7).WithName(ThelastDays, 

  thisTable.Filter(thedate.Contains(ThelastDays)).views.Average()
)

more details here : How To Coda A Moving Average. Based on a question in the community | by Christiaan Huizer | Feb, 2022 | Medium

enjoy, Christiaan

2 Likes
5

Hey Chris–this is pretty good and will be sufficient for my needs. Really appreciate the help.

However one thing is that this appears to take a mid-point type moving average rather than a trailing MA. (In trailing MA the current row + the dates directly preceding are averaged.)

Hard to say, but maybe this is due to the oldest date being on the top rather than the reverse.

The end result is effectively the same on a long enough timeline, just that the observed average will be ahead or behind a few rows depending on the number of days selected.

6

Hi @Nick_Short ,
thanks for your ‘merci’.

this logic takes only the last 7 days as you seen in the screenshot below,

image
image1549×1268 143 KB

Am I missing your point? Cheers

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.