Batch + Iterative Macro (Averaging last 6)

[salx95]

Hello Community!

Hope this post finds everyone safe and sound. I am posting this hoping someone can help me optimize my approach since for one product it is taking 4-5 minutes and I have about 600 products to batch and iterate through. 

The Data:

Date	Product	Growth	$ Value	Scenario
2020-01-01	1		50	Actual
..	2			Actual
..	3			Actual
2021-01-01	1	50%	100	Actual

The Goal:

Date	Product	Growth	$ Value	Scenario
2021-7-01	1	50%	100	Actual
2021-8-01	1	50%	100	Actual
2021-9-01	1	50%	100	Actual
2021-10-01	1	50%	100	Actual
2021-11-01	1	50%	100	Actual
2021-12-01	1	50%	100	Actual
2022-01-01	1	AVG OF LAST 6 = 50%	Last year * 1+projected growth 100*(1+50%) = 150	Projection
2022-02-01	1	AVG OF LAST 5 Act + 1 Forecast = 50%	100*(1+50%) = 150	Projection
..	2	...	...	Projection
..	3	...	...	Projection
2022-12-01	1	...	....	Projection

Using a generate rows gets me the dates for the projections to append the nulls. After that I have a really slow batch macro that takes one product at a time and inside it an iterative macro that takes All actuals + 1 forecast at a time.

I was hoping an expert knows a good and efficient way to do this in Alteryx. Any guidance is appreciated.

Thanks.

[phottovy]

Hi @salx95 ,

I'm not sure I totally understand how your current data is structured but I put together an iterative macro the will hopefully point you in the right direction. I do not believe you need both a batch macro and an iterative macro. In my basic example, each iteration will calculate the average growth over the past 6 periods for all products and multiply it by the most recent value. It will iterate into the future for 12 periods but you can adjust this number as necessary. Here is what the data looks like after the first iteration:

I wasn't sure what you meant by "Last year" in your example so my projections values get pretty big over 12 future periods. This would be easy to adjust if I understand your logic better though.

Let me know if I am on the right track at all. I'm happy to adjust my example as needed.

[salx95]

Hi @phottovy ,

Thank you so much for putting this macro together. I knew there was a better approach - I will test it with my data to see if it's faster, which from the test data it looks like it. 

Regarding the "Value" for the projections, it wouldn't make sense to use the most recent point if Growth is reflecting Year Over Year, it would need to be last year's value * (1+the projected growth). 

e.g., in your example for Product 2 - the projection for February 2022 would be the projected growth (100%+29.4% growth) * the value in February 2021 (200) = 258.94.

Hope I am making better sense now.

Thanks for all your help!