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.
I think this is the formula:
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.
By adding a bit extra you convert the numbers into dates if that feels better for you:
Thanks christian but this is sadly not working for me:
thisTable.filter(Date = sequence(thisRow.Date,thisRow.Date-7).formulaMap(todate(CurrentValue))).Views.Average()
I see your point and the below might be of help:
more details here : How To Coda A Moving Average. Based on a question in the community | by Christiaan Huizer | Feb, 2022 | Medium
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.
Hi @Nick_Short ,
thanks for your ‘merci’.
this logic takes only the last 7 days as you seen in the screenshot below,
Am I missing your point? Cheers
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.