Forward 4 weeks calculation and archiving results every week

[Anonymous]

Hello everyone,

I am trying to calculate sum of current week plus next three weeks forecast data, but the catch is that each week’s result should be stored to display the value for that week and the line gets created every week in the line chart.

Forecast data gets refreshed weekly and it is archived every week into a NEW QVD (not rewrite the previous QVDs). The reason to archive the data is to store the results of every week to display the Forward Coverage in line chart (shown below). For every week’s QVD I need to sum the forecast data for current week plus the next three weeks.

Formula for Forward Coverage and Line chart:

I need to compare the on-hand inventory data with cumulative 4-week forecast (current week plus next 3 weeks) to calculate the Forward coverage.

Please see the sample data below. I used Technical week as the only dimension, but there are some other product dimensions in the data set.

The on-hand data is CYTD data as shown below.

On_Hand:
LOAD * INLINE [
Technical_Week, On_Hand_Cumulative
2, -1800
3, 359
4, 20127
5, 115806
7, 304641
8, 496580
9, 557762
];

Forecast Data:

LOAD * INLINE [
Technical_Week, Forecast
9,  68,37
10, 22,790
11, 9,116
12, 505,597
13, 842,661
14, 842,661
15, 505,597

     .

     .

     .

     .

     .

     .

     .

57, 2,655

58, 2,655

];

Forward Coverage value for Current Week =

Sum(On_Hand_Cumulative)

/

Sum (this weeks forecast) + sum(next 3 weeks forecast)

I got stuck on how to store the value of forward coverage for each week to create the historical line until today in the line chart.

The line gets calculated as the weeks pass through.

Example line chart:

Current Technical Week = 9

Current Technical Week Start Date = 10/16/2017

Any assistance is really appreciated.

Thanks!

[Anil_Babu_Samineni]

Not sure, I understand over measure

Min({<Week = {'$(=Max(Week))'}>} ([On Hand] / [week Forecast]),1) * 4