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

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.