Running Average of past 12 months for every data point

[KantianKinetics]

I've seen a lot of examples of running average for a year, restarting at Jan 1, but I'm having trouble generating the running average of the past 12 months.  I don't believe now() function should be used at all since its more of an offset() function.  

This doesn't work since it bases it off of the now() date. 

$a.runningAggregate(average(),condition(1y, capsule(now()-1y, now())))  

This doesn't work since it aggregates from Jan-1 thru the date.  

$a.runningAggregate(average(),years())

This doesn't work since it aggregates based on the origin date, being the 1st of the year

$a.runningAggregate(average(),periods(1year,1year,'2016-01-01'))

I'm not sure if this does what I want

$a.runningAggregate(average(),$monthly.beforeStart(1year))     

where monthly is a periodic condition of each month.  Even so, I don't want the 12 month rolling average to calculate from the beginning of each month, rather at the exact date of the date.  And I don't think the formula is even doing the rolling average correctly anyways.  

 

Thanks a lot!

Edited December 18, 2023 by KantianKinetics

[Chris Harp]

To create a 1 year period that resets daily at midnight, you can use the following formula:

periods(1y, 1d, '2024-01-01', 'America/Chicago')

When creating your rolling average calculation, use the endKey() in your aggregate function.

$signal.aggregate(average(),$1yearPeriod,endkey())

Let me know if you have any questions.