Forecast Analysis Based on Histoircal Moving Average Weekly Data

bgrossman1

Hi,

I am doing a forecast analysis, I need to to be able to determine two things. First, let me describe the data a bit.

I have historical data grouped by warehouse type. I am trying to calculate the projected volume for all 2020 weeks that are null. I need to calculate the forecasted volume by taking the average of all previous 2020 divided by all previous 2019 weeks for that specific warehouse type. I then need to multiply that number by the same week number in 2019 you are forecasting for in 2020. The same calculation above will need to continue to repeat for all remaining null values in 2020.

Can this be done in alteryx? 

Any input would be greatly appreciated. Thanks!

Brett

Forecast Averages.xlsx

jdunkerley79

Yes, this is possible. I suggest:

First, sort by XL FC and order_date week

Next, create a running total for the 2019 figure (using a Running Total tool grouped by XL FC)

Then you can use a MultiRow formula tool to create a forecasted 2020 running total with an expression of:

iif(isnull([2020_Avg_Sum_shipments ]),
    [Row-1:RunTot_2020_Avg_Sum_shipments]/[Row-1:RunTot_2019_Avg_Sum_shipments]*[2019_Avg_Sum_shipments],[2020_Avg_Sum_shipments ])
+[Row-1:RunTot_2020_Avg_Sum_shipments]

again grouping this by XL FC

Finally, another MultiRow formula tool can be used to covert this running total into the missing 2020 values:

iif(isnull([2020_Avg_Sum_shipments ]),[RunTot_2020_Avg_Sum_shipments]-[Row-1:RunTot_2020_Avg_Sum_shipments],[2020_Avg_Sum_shipments ])

Have attached a sample, hopefully enough for you to get going.

Forecast Based on Running Avg.yxmd