Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Dynamic index match to turn forecast into hindcast!

Alteryx Partner

Alteryx gurus,

 

I am trying to take several rows of 6 month forecast data and, create a forecast history for a given month.

 

For example, each row currently shows a 6 month prod forecast for a given SKU (top table). I want to create a row that tells me what the forecast for a given month was at 1 month out, 2 months out, 3 months out etc (similar to the bottom table).

 

Any tips to point me in the right direction?

 

Thanks!

 Collapse.PNG

Highlighted
Magnetar

You may need to do some field sorting and renaming at the end to make this work exactly how you want, but a combination of the transpose and crosstab tool should be able to get you where you want to go.


If you transpose your forecast data with ITEMNO, ForecastDate, Month, and SKU/Month ID as "Key Fields" with everything else as a Data field, you will get 1 row per forecasted month.

I was able to do some data cleansing/conversion to turn the "Name" column - Prod start fcst +x month into just the number of months, and then use a DateTimeAdd formula to create a field called Forecastedmonth.

I did something similar with each month that we were creating history for, creating a field called "ForecastFromMonth"

Then I joined the two together on ForecastedMonth to Forecasted month, ForecastDate to ForecastFromMonth, and ItemNo to ItemNo

From there, I cross tabbed the "J" output, grouping on ItemNo, Forecasted month, Month, and SKU/MONTH ID from the history input, to pull in the values.

 

ForecastCompare.PNG

 

As you can see, my forecast_from_month fields are out of order (reversed from your order) and their names aren't quite perfect.  You can use a Select tool after this to rename them and resort.

Highlighted
Magnetar
Magnetar

An example of something I believe is similar to @Claje solution attached! Transpose + Cross-Tab tools are definitely the key to this one... let us know if you need some additional assistance!

 

Cheers,

NJ

Highlighted
Alteryx Partner

Awesome, thanks!

Highlighted
Alteryx Partner

Fantastic! Thanks

Labels