Calculating forecast stock in QV Script

Wed, 20 Jul 2011 09:24:30 GMT

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:

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 — Wed, 20 Jul 2011 11:22:23 GMT

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

topic Calculating forecast stock in QV Script in QlikView