Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Forecast Analysis Based on Histoircal Moving Average Weekly Data

bgrossman1
5 - Atom

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

1 REPLY 1
jdunkerley79
ACE Emeritus
ACE Emeritus

Yes, this is possible. I suggest:

 

jdunkerley79_0-1602009521786.png

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.

Labels