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:
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:
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.
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.
Here’s a dummy page:
I have just work through the same topic with
@Craig_PaxsonTN in this Community post:
Get last n rows from a linked table.
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:
a while ago I showed how to deal with something alike:
Hope it helps, Cheers, Christiaan
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.
Thanks so much for the response. I think I’m still struggling with this a bit.
I can’t seem to get the rank to work properly.
Maybe we can start more simply:
How do I get column that will reference the previous month’s date for that particular client?
@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
Thanks so much for responding!
When I try the filter from that article, I still get the same out of order index.
Here is how I did it…
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.
Here is the link to the doc.
I also noticed the contribution of
@Craig_PaxsonTN and I don’t want to stand in his way.
My contribution is rather simple:
I created this one in the steps below, the above image is a view of the table you see below:
As from here, you could try to add a controller and link it the data so you see the last 3 months for example.
the last addition is not related to the rolling total question. It is filter that looks for the months that relate to the slider value.
Great take - our problem, therefore solutions are different.
My problem requires an ordered set of data to be accurate.
Definitely yours is easier if an ordered set is not required,
can we consider this topic as closed? I would welcome your feedback, cheers, christiaan
Thanks so much for the responses!
I’m looking into this now. I’m looking for a rolling 3 month average, so just going to see how to calculate that instead of the rolling sum.
Also this is going to be very bulky when I include all the actual data which is much larger than this sample.
@Peter_Griffin , thx for coming back to me.
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.
Ah, sorry I noticed the screenshots the first time, but forgot to reference them earlier today.
I got that all to work, now I just need to figure out how to make it roll as time goes on.
Thanks so much for all your help!
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?
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.