Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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_2Beginning of Week InventoryPOS ProjBeg Inv less POSRequired End of Week InventoryPurchasesEnd 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



1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

7 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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!

Not applicable
Author

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