Moving Average using multi-row formula tool
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Please take a look on this posts :
Hope this helps,
Regards
