Moving Average using multi-row formula tool

Question

Hi,

My data set has two fields: 1) date, organized by day (ex: 9/9/2021,9/10/2021,9/11/2021) and 2) price, for each date. I am looking to take a yearly moving average of the price for each date - for example for the 9/11/2021 date, I would like the price to be the average of prices from 9/11/2020-9/11/2021.

I know that I can use the multi-row formula tool, however that would mean I would have to manually write an expression like the following that would take a long time to do:  Average ([Row-365: Price],[Row-364:Price],.....,[Row-1:Price],[Price])

Is there a way that I can create the above expression much quicker on the multi-row formula tool or by using another tool?

Thanks!

messi007 · Answer

@nabdulk,

Please take a look on this posts :

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Moving-average-calculation-over-a-date-range/td-p/350663

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Is-there-a-way-to-create-running-averages/td-p/114372

Hope this helps,

Regards

AngelosPachis · Answer

Hi @nabdulk ,

You can find the moving average by first parsing out the day and month out of each date, which will act as your field to group by. Then you can use a running total tool to add the values for each day and month per year and then divide it with the year count which will yield the moving average you 're after.

I also included a way on how you would calculate a plain average, just in case it's of any use.

Best,

Angelos

example.yxmd