Rolling 12 Month Average in Magic ETL

Robaba04

Hi there!

Was hoping someone would have a solution for what I am trying to do. I want a 12 month historical average in line with the current month in my dataset. (Sample datasets will only show 3 month)

This is a sample dataset of what I am working with:

*"Desired Column" would be the field that holds the historical average.

In this case the Desired Column in the first row = 33.3 because:

1. Add all the "Type" = Cloth and "Name" = Chest has a weight of (33.9 + 33.3 + 32.6)

2. Divided by the last 3 months (99.8 / 3) = 33.3

Month-end Date	Country Name	Type	Name	Weight	Desired Column
31-Mar-2024	United States	Cloth	Chest	33.9	33.3
31-Mar-2024	United States	Cloth	Boots	33.5	33.1
31-Mar-2024	United States	Leather	Chest	36.2	35.2
31-Mar-2024	United States	Leather	Boots	37.0	35.7
29-Feb-2024	United States	Cloth	Chest	33.3	33.3
29-Feb-2024	United States	Cloth	Boots	33.3	33.1
29-Feb-2024	United States	Leather	Chest	35.6	35.2
29-Feb-2024	United States	Leather	Boots	35.9	35.7
31-Jan-2024	United States	Cloth	Chest	32.6	33.3
31-Jan-2024	United States	Cloth	Boots	32.5	33.1
31-Jan-2024	United States	Leather	Chest	34.0	35.2
31-Jan-2024	United States	Leather	Boots	34.2	35.7

I am looking to get this done within Magic ETL using whatever tiles makes the most sense.

Any help would be appreciated!

Thank you in advance!

MarkSnodgrass

You are welcome @david_cunningham ! Keep up the great work on the answers!

david_cunningham

@MarkSnodgrass has a fantastic YouTube video on this exact topic! Check it out 😁

The video covers 30/60/90 day rolling windows, but you can adapt this to your particular requirements.

Thanks Mark for creating this great video!

https://www.youtube.com/watch?v=sLAy-9pu0VM

MarkSnodgrass

You are welcome @david_cunningham ! Keep up the great work on the answers!