Projecting Stock Out

Fri, 14 Jun 2013 12:58:07 GMT

Hi,

I have a problem of simulating a Stock out.

Country	Location	Partnumber	Date	2013-06-13	2013-06-14	2013-06-15	2013-06-16	2013-06-17	2013-06-18	2013-06-19
SE	SE10	SE80060	Inventory	69,3	0,0	0	0	0	0	0
SE	SE10	SE80060	Replenishment	0,0	0,0	0	0	0	0	0
SE	SE10	SE80060	Simulated Sales	30	30	0	0	0	0	0
SE	SE10	SE80060	Projected Stock	39,32772	9,32772	0	0	0	0	0
0	0	0	Inventory	0	0	0,0	0,0	0,0	0,0	0,0
0	0	0	Replenishment	0	0	0,0	0,0	0,0	0,0	0,0
0	0	0	Simulated Sales	0	0	30	30	30	30	30
0	0	0	Projected Stock	0	0	-30	-60	-90	-120	-150

So I have:

* The current Inventory

* Planned replenishment

* Simulated projected sales based on last 90 days of Sales (set as a variable using set analysis)

Now I can't get the dates to be continous, in fact for all records where replenishment doesn't exist I want to populate the simulated sales per date. Using "show all values" in the dimension tab of the pivot I get the above table as first pivot shown. If I untick the "show all values" option, I get the following table (note that there are records in the main table, eventhough shown as '0' here in replenishment basically replenishment is a very small number):

Country	Location	Partnumer	Date	2013-06-13	2013-06-14	2013-07-05
SE	SE1012	SE80060	Inventory	69,3	0,0	0,0
SE	SE1012	SE80060	Replenishment	0,0	0,0	0,0
SE	SE1012	SE80060	Simulated Sales	30	30	30
SE	SE1012	SE80060	Projected Stock	39,32772	9,32772	-20,67228

But basically I want the last 4 rows to be merged into the first 4 rows. For all records where replenisment exists (although 0 in my example above) the projected stock is calculated correctly using RangeSum and Before. But for records that is missing between different replenishment i want to fill out with the dates and the simulated sales.

Any idea on how to proceed ?

Thanks,

David

topic Projecting Stock Out in QlikView

Projecting Stock Out