I am pulling some state covid testing data into Dundas with a Python transform, and want to calculate a rolling 7 day avg for the data. I have calculated this internally in SQL Server for our own data using something including -
, AVG(DailySum) OVER (ORDER BY [CreateDate]
ROWS BETWEEN 6 PRECEDING AND 0 FOLLOWING) SevenMovingAvg
and want to do something like this with the State data, which includes :
I looked at the moving average formula in Dundas but I don’t want a specified number of periods but for each period to be 7 days for an ongoing period of time. I would like to create a calculated element to create a new column which adds the new_positives from test_date to the new_positives from the 6 days leading up to it and then divide by 7 to create a rolling avg… but am struggling with figuring out how to do this.
I’ve been scouring for solutions to this in C# and am just stuck, does anyone have any ideas ?