QlikView for Number of Weeks Stock based on a Forecast

[Dayna]

Hello!

I am hoping that you can help me with a task I've got within QlikView.

I am trying to calculate the number of weeks stock I have for each product based on the stock available agaisnt the forecast.

The forecast has been extracted from an array, in a 52 week period. I am going to assume that the data I have been provided is accurate for today, therefore I would like to work out how many weeks stock I have in comparison to the forecast.

The fields I currently have are:

Stock Item
Available
Changed Amount
On Hand
Ordered
Required

Forecast Amount (per Part)
Week
Year

If you could please help me with the above, it would be most appreciated, as I'm not entirely sure where to start!

Many thanks,

Dayna Litherland

[johnw]

There are probably a ton of ways to approach this. Here's one way.

Edit: OK, still can't attach anything. This is driving me nuts. Anyway, here's the script.

[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"
;

[Dayna]

-

[Dayna]

-

[Dayna]

Hello John,

Many thanks for your response, I have integrated your code into the application, but I have a few enquires/issues...

We operate on a 52 week fiscal year, which starts mid July, with the forecast altering depending on the week. How could I set QlikView to work out the current week, and deduct the forecasted sales stock from the current available stock on a week basis - so it would countdown off the forecast until 0 weeks remain?

It unfortunately isn't showing the data correctly, this it the code I have thus far:

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

;

Your help is as always, appreciated!!

Kind Regards,

Dayna