Inventory and Purchases Calculation

[#1]

Hi Everybody,

I'm trying to create a QlikView file that will tell me how much of an item needs to be purchased every week based on current inventory and our retail sales projections.

I have current inventory, sales projections, and an end-of-the-week inventory requirement that is equal to the sum of the next four weeks of sales. For example, if we are going to sell 500 units over the next four weeks, my inventory at the end of this week must be 500.

I have attached an example in Excel of what I am trying to do but I have been unable to recreate it in QlikView.

The data in yellow below is the dataI have for each product. Everything else is calculated. The biggest problem I'm having is trying to get the beginning of the week inventory to equal the ending inventory for the previous week. I was trying to get everything calculated in the script but I don't know if this is the best way or not.

I need to be able to calculate the purchases for multiple items. The "QlikView Upload" worksheet in the Excel file is an example of the format that I have been given.

Any help would be greatly appreaciated.

Thanks,

Justin

Product_2	Beginning of Week Inventory	POS Proj	Beg Inv less POS	Required End of Week Inventory	Purchases	End of Week Inventory
May_Week1	1,200	785	415	3,089	2,674	3,089
May_Week2	3,089	637	2,452	2,994	542	2,994
May_Week3	2,994	677	2,317	3,012	695	3,012
May_Week4	3,012	990	2,022	3,280	1,258	3,280
Jun_Week1	3,280	690	2,590	3,232	642	3,232
Jun_Week2	3,232	655	2,577	3,211	634	3,211
Jun_Week3	3,211	945	2,266	3,307	1,041	3,307
Jun_Week4	3,307	942	2,365	3,347	982	3,347
Jun_Week5	3,347	669	2,678	3,098	420	3,098
Jul_Week1	3,098	751	2,347	3,258	911	3,258
Jul_Week2		985
Jul_Week3		693
Jul_Week4		829

[#2]

Hi!

Since I was also wondering if what you were asking was possible, I just started, and I think I have what you're looking for.

If it can be done easier, I'd like to hear...

Hope it helps; let me know if it is clear what I've done.

Roberto

[#3]

It looks like that will only work when using a single product. In my actual file, I have 4,000 products that I for which I need to find the required purchases. The "QlikView Upload" worksheet is how the data is formatted in the file I have been given.

I don't think your example will work with multiple products since there are multiple values for what is currently on hand.

[#4]

Also, it looks as though this is assuming that purchases are required every week which may not be the case for every product. If I change the on hand from 1,200 to 4,000 then the purchases should be zero for the first week and 416 for the second. However, QlikView returns purchases of -126 for week one and 542 for week two.

The inventory at the beginning of the week may not always equal the POS projections for the next four weeks if there is excess on hand.

[#5]

I fixed one of your 2 issues, being the ability to load multiple products. See the new zipfile attached.

The other issue; I think it can be solved, but I will not do it (at least not very soon). It is quite complicated compared to excel, and taking to much time / coding.

Perhaps you can continue from here, or perhaps there are others with quicker ideas.

Hope the attached helps.

[#6 · Accepted solution]

Ok, I think I have all problems solved in my newly attached qvw. Please check and let me know :--)

Let me be clear: the current script can  be optimized (i believe I'm doing 6 residents?!)

I didn't take the time for it.

Since this is (as far as I can see) a working solution, you can start optimizing this.

I hope it helps. Good luck!

PS: after typing this message I rechecked the code and saw that the for loop (line 154 reads this: For zi=1 to 20 where 20 is hardcoded). That can easily be replaced; let me know if help is needed. I hope you can first check the results!

Roberto

[#7]

It works!! This is awesome. Thank you very much.

It took me a little while to figure out how I could apply it to what I'm using. There's a lot of stuff in the script that I've never used before but it all works.

Also, I changed the zi part to "For zi=1 to peek('globalCounter',-1,'test10').

Thanks again!

[#8]

Hi jpkelly84,

Great to hear that it works now.

I also learned from it: the for...Next loop construction, in which every record is treated separately was also new for me.

Good to hear you fixed that last issue.

Roberto