Countdown of stock

[Dayna]

Hello!

I had posted previously, and John supplied me with some code that really helped, but I believe I need some further assistance…

Scenario:

I am trying to calculate the number of week's stock I have for each product based on the stock available against the forecast.

The forecast is extracted from a 52 week array, in a field called Qty. Within a table called Inventory, I have fields for Available Stock, Required, On Hand and On Order. These need to be calculated into my available stock.

The forecast is in calendar months, years 2008 - 2010 in 52 weeks. I need QlikView to work out what current week we are in, as I need it to count down from there. I then need QlikView to countdown my stock levels, updating the amount for each week until 0, showing what week it will run out.

The inventory table is a standard figure which is updated real time (no time period).

The code John originally supplied was:

[Inventory]:
LOAD * INLINE [
Stock Item, Week, Forecast Amount
1, 1, 20
1, 2, 10
1, 3, 30
1, 4, 60
1, 5, 10
2, 1, 1
2, 2, 2
2, 3, 3
2, 4, 5
2, 5, 8
2, 6, 13
2, 7, 21
2, 8, 34
2, 9, 55
2,10, 89
3, 1, 10
3, 2, 10
3, 3, 10
3, 4, 10
3, 5, 10
3, 6, 10
3, 7, 10
3, 8, 10
3, 9, 10
3,10, 10
3,11, 10]
;
LEFT JOIN
LOAD
"Stock Item"
,"Week"
,rangemax(0,if("Week"=1,ceil(rand()*100),peek("Available")-previous("Forecast Amount"))) as "Available"
RESIDENT [Inventory]
;
[Weeks of Stock]:
LOAD
"Stock Item"
,min("Week") - 1 as "Weeks Available"
RESIDENT [Inventory]
WHERE "Available" <= 0
GROUP BY "Stock Item"
;

Which I integrated to make:

Inventory:

LOAD "in_domain",

"in_part" as Part,

"in_qty_all" as Quantity,

"in_qty_avail" as AvailableStock,

"in_qty_chg" as Changed,

"in_qty_oh" As [On Hand],

"in_qty_ord" as Ordered,

"in_qty_req" as Required,

"in_site";

SQL SELECT "in_domain",

"in_part",

"in_qty_all",

"in_qty_avail",

"in_qty_chg",

"in_qty_oh",

"in_qty_ord",

"in_qty_req",

"in_site"

FROM PUB."in_mstr";

TempForecast:

LOAD fcs_fcst_qty as Qty,

"fcs_year" as Year,

"fcs_part" as Part;

SQL SELECT

"fcs_fcst_qty",

"fcs_part",

"fcs_year"

FROM PUB."fcs_sum";

Join (Inventory)

Load Part,

Year,

Year&'.'&num(iterno(),'00') as YearWeek,

Subfield(Qty,';',Iterno()) as Qty,

Iterno() as Week

Resident TempForecast

WHILE iterno()<substringcount(Qty,';')+2;

Drop Table TempForecast;

LEFT JOIN

LOAD

Part

,"Week"

,rangemax(0,if("Week"=1,ceil(rand()*100),peek("Available")-previous("Qty"))) as "Available"

RESIDENT [Inventory]

;

[Weeks of Stock]:

LOAD

Part

,min("Week") - 1 as "Weeks Available"

RESIDENT [Inventory]

WHERE "Available" <= 0

GROUP BY Part

;

You're help will be muchly appreciated!!!

Kind Regards,

Dayna Litherland