Calculating forecast stock in QV Script

[#1]

Hello,

I have a design issue and I wondered if someone could help me here.

To simplify my structure, I have a fact table containing:

• Week
• Measure_Type (Actual or Forecast)
• Forecasted Production
• Forecasted Sales
• Actual Stock
• DataSet (the week at which the Forecasted data are available in the system).

So:

• for each week, we have only one Actual Stock figure
• for each week, we have several possible Forecasted figures (one for each dataset)

Once this table loaded, I need to calculate the Forecasted Stock for next weeks. the formula is simple:

     [Forecasted Stock W3] = [Actual Stock W2] + [Forecasted Production W2] - [Forecasted Sales W2]

And I don't know how I can do this. How can I access the data from last week during the script ?

I tried to use a tmp Fact table and to the load it into the final one with a field "NextStock" and the formula:

[Actual Stock] + [Forecasted Production] - [Forecasted Sales]

But as lines are not the same, the forecast line does not find the Actual Stock value.

I thought about creating a Mapping table to get the stock, but I have millions of lines and a lot of other dimensions.

Does someone have an idea on this ?

Thanks

Pascal

[swuehl]

Hi Pascal,

maybe like this

I assume that you need the sum of the forecasted production and sales per week, right?

You could sum this information with loading the resident fact table grouped by week, where Measure_Type = Forecast.

Then you might load your fact table again and lookup()  the forecasted values for the given week.

Does this makes sense?

Stefan