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

    Inventory and Purchases Calculation

    Justin Kelly

      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



        • Re: Inventory and Purchases Calculation
          Roberto Postma

          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
              Justin Kelly

              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
                Justin Kelly

                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
                    Roberto Postma

                    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
                        Roberto Postma

                        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