7 Replies Latest reply: Jun 28, 2012 7:57 AM by Roberto Postma

# Inventory and Purchases Calculation

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
• ###### Re: Inventory and Purchases Calculation

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

• ###### Re: Inventory and Purchases Calculation

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.

• ###### Re: Inventory and Purchases Calculation

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.

• ###### Re: Inventory and Purchases Calculation

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.

• ###### Re: Inventory and Purchases Calculation

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

• ###### Re: Inventory and Purchases Calculation

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!

• ###### Re: Inventory and Purchases Calculation

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