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!

Here is a sample of the data:

date_start country ratio
2024-06-17 AE 0
2024-05-05 CI 0.6192369
2024-02-01 IT 0.151455
2024-03-01 IE 9.333131e-02
2024-06-18 LA 0.120377
2024-06-25 GM 0
2024-06-16 KE 7.264207e-02
2024-05-02 JP 7.534477e-02
2024-07-30 LT 0.192145
2024-02-01 BA 0.4514098
2024-04-07 AF 0.2507785
2024-06-13 KY 0
2024-06-02 GH 0.1100592
2024-07-23 IN 0.3530481
2024-05-31 LS 6.511628e-02
2024-04-24 ES 0.2700332
2024-06-21 FO 0
2024-04-11 IQ 0.7298055
2024-05-13 GM 0.7730376
2024-08-06 KZ 0.2948061

[cpet13]

@AKPWZ check out this discussion board. It gives a macro you can use, as well as an alteryx workflow you can download that may give you a good idea. Solved: Re: Rolling Average Based on Dates - Alteryx Community

[Qiu]

@AKPWZ 
So I interprete that you want to the 30-day rolling average for each country.
I made a batch macro for country and average one also.

[alenmick]

---

@AKPWZ wrote:

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!

Here is a sample of the data:

date_start country ratio
2024-06-17 AE 0
2024-05-05 CI 0.6192369
2024-02-01 IT 0.151455
2024-03-01 IE 9.333131e-02
2024-06-18 LA 0.120377
2024-06-25 GM 0
2024-06-16 KE 7.264207e-02
2024-05-02 JP 7.534477e-02
2024-07-30 LT 0.192145
2024-02-01 BA 0.4514098
2024-04-07 AF 0.2507785
2024-06-13 KY 0
2024-06-02 GH 0.1100592
2024-07-23 IN 0.3530481
2024-05-31 LS 6.511628e-02
2024-04-24 ES 0.2700332
2024-06-21 FO 0
2024-04-11 IQ 0.7298055
2024-05-13 GM 0.7730376
2024-08-06 KZ 0.2948061

---

To calculate the 30-day rolling average of the "ratio" column by country for each day, you can use a window function in SQL.

[aatalai]

The moving summaries tool from crew macros could also do the trick

http://www.chaosreignswithin.com/p/macros.html