Dynamic index match to turn forecast into hindcast!

[glenn]

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!

 

[Claje · Accepted solution]

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.

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.

[NicoleJohnson · Accepted solution]

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

[glenn]

Awesome, thanks!

[glenn]

Fantastic! Thanks