Need help with Report for Lag Forecast

Hi All,

I need your inputs in creating a Report to show Lag Forecast.

Forecast is done on a weekly basis. Assuming forecast is done at a time for 3 upcoming weeks, the data in the cube will be as follows:

ZCALWEEK CALWEEK MATERIAL FORECAST

46 47 ABC 2

46 48 ABC 3

46 49 ABC 6

47 48 ABC 11

47 49 ABC 8

47 50 ABC 14

48 49 ABC 10

48 50 ABC 2

48 51 ABC 5

49 50 ABC 4

49 51 ABC 6

49 52 ABC 9

Now the calculation for lag is as follows:

Lag1 for Week 47 = Forecast for Week 47 done in ZCALWEEK 46

Lag2 for Week 47 = Forecast for Week 47 done in ZCALWEEK 45

Lag2 for Week 48 = Forecast for Week 48 done in ZCALWEEK 46

User wants to run the Report for a range of Calweeks.

For the Selection - Calweek: 47-49 Report should be as follows:

CALWEEK 47 48 49

Material LAG1 LAG2 LAG1 LAG2 LAG1 LAG2

ABC 2 11 3 10 8

To achieve this we used the variable for Calweek and offset its value.

So Lag 1 = Forecast restricted by week 47-1

Lag 2 = Forecast restricted by week 47-2

This approach of offsetting variable only works for the first week that is week 47.

In the subsequent weeks the value is offset again to the same value as first week.

For example in Lag2 for Week 49 = Forecast for Week 49 done in ZCALWEEK 47. However the offset approach fetches value of the Forecast done in week 45.

Please advice as to how this might be solved.

Is it possible to show the Report for a range of weeks or is it only feasible to show data for one week at a time?

Thanks in Advance,

Amrita