I’m trying to calculate a 3 month rolling average for a column.
There’s a previous response on here about moving averages, but I don’t think the solution applies anymore:
My table has a group column on the left for “Clients”
and then a date column with the 1st of every month for each row.
I’m looking to return average the Ad Spend (another column) for the past 3 months.
HOWEVER
I only want rolling averages per each client, so it can’t roll back into previous clients.
AND the rowIDs are all out of order, so I can’t use that either.
The solution I propose in the document I share there makes use of a temp storage table, Craig has figured out a way to calculate without the temp table. I have asked him to share his solution.
If you are on a rush though, you can use what I shared in this doc:
Yes, I linked the community post from your article in my post.
The challenge I’m running into is that the row indexes are all out of order, so I can’t use them. I’m trying to use date but struggling to figure that part out.
sorry @Peter_Griffin , i didn’t notice that
related to the out of order index, that issue should be solved applying a filter.
I wrote last week about this issue:
the focus is to put in place a filter that restores the index.
Hope it helps, cheers, Christiaan
Parent table is called Metrics. Child table is called Metrics Tracking.
In Metrics Tracking I added 3 columns:
Metric Name: formula - thisRow.Metric.Name
Metric Rank: formula - rank(thisRow.Date,thisTable.Date)
last n by Metric: formula - Filter(thisTable, Metric=thisRow.Metric)
.[Metric Rank].Sort(true()).Slice(1, [Sample Size]).ReverseList()
This last column creates a list of the last n rows ordered by date.
I have a control called Sample Size where I can change the sample size dynamically.
On the parent Metrics table, I added these columns:
Last n Metrics Tracking: formula - thisRow.[Metrics Tracking].Filter(Metric.Name=thisRow.Name).[Last n by Metric].First()
This formula copies the ordered list from Metrics Tracking.Last n by Metric to the Metrics table
Last n Values: formula - thisRow.[Metrics Tracking].Filter(And(Metric.Contains(thisRow),In([Metric Rank],thisRow.[Last n Metrics Tracking]))).Sort(True(),[Metrics Tracking].Date).Value
This formula copies the values from the Metrics Tracking table, based on the previous column.
MR Values: formula - Sequence(1,thisRow.[Last n Values].Count()-1).FormulaMap(abs(thisRow.[Last n Values].Nth(CurrentValue+1) - thisRow.[Last n Values].Nth(CurrentValue)))
This formula creates the moving range between each value in the above column (value 1 - value 2, value 2 - value 3,…)
Now that we have the MR values, we can calculate the average moving range, or anything else.
The rolling total is a different issue compared to the average of the last three months.
The latter I did not create in your doc but I pasted the solution in my response. Could you have look? it is not too difficult, but it follows a different pattern as I show in the screenshots.
Many makers with a spreadsheet logic got stuck in these issues I am afraid.
the slider sets the months back and the last month is the reference, so when you add a month, the slider moves with it.
the main trick is to relate the slider value to the month part of the date() function and to set the dates as the first of the month. In case you want to look into the past let’s say you want to see what happend in nov, oct & sept, you can create a controller on the canvas that shows the reference month (december) and you create a function that relates to this month of choice.
I guess that by now you understand how this works and we can close this thread?
