Calculate a moving average using a daily value for the past year

[Renfree]

Hey Alteryx Community,

I am trying to get the value for the average headcount for the previous year to use in an annual attrition calculation - I have been able to acheive this in snowflake with the following window function:

'avg(headcount) OVER (PARTITION BY field_1, field_2, field_3 ORDER BY run_date ROWS BETWEEN 346 PRECEDING AND CURRENT ROW) as twelve_month_headcount'

I want to be able to report the annual attrition on a daily basis, I am trying to replicate this in Alteryx using a multi-row formula, but I cannot find a way to do this without having to use row-1, row-2, row-3, row-4.... row-n.

Is there a way to do a rolling average for a large number of rows without having to add each row as it's own variable?

Thanks

[Christina_H]

Best way I can think of is to use an iterative macro to calculate the average for each group of rows.  The attached generates random data, then for each row calculates the average of that row with the previous 4.  You would need to make sure the number of iterations is large enough to cover your whole data range.

[Renfree]

@Christina_H Thanks for this, it works really well for iterating over the 365 rows - I am struggling to replicate the 'Partition By' section of the snowflake query though - are you able to advise on how I could achieve this using the Moving Average Macro you shared?

[Christina_H]

I'm not familiar with snowflake, but are you grouping by other fields?  Here's an updated version.  To add more fields you would need to update the macro input and first summarize tool in the macro, and the join tool in the main workflow.

[Renfree]

@Christina_H that's correct - thanks for updating! I have used the tool with the new group function, I had 3 fields I wanted to group by so used a formula to convert them into one column called

Group =  [Field 1] + " - " + [Field 2]  + " - " + [Field 3]

I then used the Group field in the Macro input as demo'd, along with a running count grouped by this new column as the RowID.

I have daily data going back to 1st August 2021 to calculate an annual average value of the daily headcount my first complete data point becomes the 1st August 2022 which is far enough back - I am however encountering an error now where the iteration limit is 500.

I tested this in the Macro as well by setting the condition expression for the generate rows function to RowID < 501 and was able to replicate the error. Do you know if this is a fixed limit in Alteryx?

[Christina_H · Accepted solution]

There is a limit in the macro but you can change it to an appropriate value.  In the macro, open the interface designer from the menu (or ctrl+alt+D), and in the properties you can set the max number of iterations.