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.
|Product_2||Beginning of Week Inventory||POS Proj||Beg Inv less POS||Required End of Week Inventory||Purchases||End of Week Inventory|