Create table in load and fill columns with values from existing tables
I am struggling how to do this. I have a Forecast Table with
Forecast Cycle (2020-M03)
(A forecast Cycle will forecast for 24months in future)
I have a sales table with
I want to create a final table comparing the Sales minus Forecast For each Year month Product Customer group combination.
In Final Table i want 12 columns (Forecast -x) and in those i want to populate;
For the current row where Month Year = 2020-01 I want the column Forecast-1 so be populate with the Sum of the value forecasted in the cycle of Month year -1 for that MonthYear and its Product/Group combination. and so on for the Forecast Cycle of Month year -2.
i.e if looking at 2020-03 year month; in Forecast -1 i expect to see the value of that was forecast in 2020-02 version but for the month 2020-03
I am really struggling here; i have tried to pivot the Forecast data so that it is also on month year level.
I am thinking a for loop is needed here but i am very weak in this area.
Thanks so much for your quick reply. i think now have my data in the correct format (below) where i have my sales and the forecast values together by year month.
But how can i create and populate new columns Forecast -1,-2 etc. using this new table.
I want; that if the row's Year month value is 2020-03; then i want to populate the column "Forecast-1" with the value in the column "2020-02" for that row and repeat this for the row; up until Forecast -12.