How to calculate the 30-day rolling average?

[AKPWZ]

Hi,

Could someone please help me calculate the 30-day rolling average of the "ratio" column by country for each day?

I have tried using the multi-row formula, but it's a bit confusing, and I'm not sure if I'm doing it correctly. Any guidance would be appreciated.

Thank you!

Attached sample of the data:

[OllieClarke · Accepted solution]

Hey @AKPWZ 

Here's how I'd do this:


The multi-row method can look back 30 rows, but that's not the same as 30 days in your data (it's also a massive pain to write such a long formula).
By scaffolding your dates to go back 29 days and then join your data onto itself with that, we can average that joined data to find an actual 30 day moving average.

If you need to find this average within countries (i.e. the 30 day moving average in DM) , then include country as a joining condition, but I've assumed that's not what you're after here...

Hope that helps,

Ollie

[AKPWZ]

Hi @OllieClarke Thank you Thank you so much

I just want to confirm that I understood the workflow you shared correctly. This workflow will:

1. Create a Date Range: For each date, look back 29 days to include a total of 30 days, including the current day.

2. Join Data: Merge your data with itself based on this 30-day period.

3. Calculate the Average: For each date and for each country, compute the average of the "ratio" values over these 30 days.
   As you asked (If you need to find this average within countries (i.e. the 30 day moving average in DM) , then include country as a joining condition) and If that’s what I need, what changes should I make to the existing workflow?

This will provide a 30-day moving average for the "ratio" column.

[OllieClarke · Accepted solution]

@AKPWZ 

Yeah, so the generate rows makes a lookup of all the dates which would be in the 30 day window


We then use that lookup to join on the relevant ratio values



Then you can avg the Right_Ratio column to find your 30 day moving average.

Does that make sense?

[AKPWZ]

HI @OllieClarke 
If I need to find this average within countries (i.e. the 30 day moving average in DM) , then include country as a joining condition.
so in the joining tool I also check country data to achieve this, right?  (Attached the screenshot)
Also could you please tell me what exactly DM is? Thank you

[OllieClarke]

@AKPWZ 

Exactly like that screenshot!

DM was an example value in your country column (maybe Dominica?)

[AKPWZ]

Hi @OllieClarke thank you once again

[OllieClarke]

Happy to help @AKPWZ - would you mind marking my response as a solution, so the community can more easily find it if they've got the same question?

Cheers

[rk6755]

Hey Ollie,

On similar lines to above query, I have this dataset and I would like to calculate 12 months rolling average of  Volume_LTE_GB (columnD) per USID (columnC). Can you please guide me through this.

Thank you in advance.