1 Reply Latest reply: Nov 23, 2017 6:11 PM by Mikhail Bespartochnyy RSS

    Tracking Inventory Levels

    Mikhail Bespartochnyy

      Hello everyone,

       

      I'm having trouble figuring out how to calculate a running inventory level based on quantity of an item purchased and sold and few other events. In summary, each day purchases, sales, and other events are recorded per product. I got to the point where I created an inventory table with all available dates for the life of a product, a product ID, purchase price and then quantity that was purchased, sold, etc. each day.

       

      Now I'm at a point where I need to create a calculated column that shows quantity of a product that is available in inventory on a given day. It's very simple to do in Excel, but I'm struggling to get the column created in QlikView script. Attached is the app and a sample spreadsheet that I'm working with. Quantity In Stock (Column J) is what I'm trying to create in script.

       

      Does anyone know of a way to get that done?

       

      Thanks,

       

      Mikhail B.

        • Re: Tracking Inventory Levels
          Mikhail Bespartochnyy

          Solved my own problem. I keep forgetting about Peek function. All that's need to be done here is a preceding load with an IF statement that references previous Quantity In Stock value:

           

          Inventory:
          LOAD *,
          If([Product ID] = Peek([Product ID]),
               
          RangeSum([Quantity Change], Peek([Quantity In Stock])),
               
          [Quantity Change])                                 as [Quantity In Stock];
          LOAD Date,
               
          [Product ID],
               
          [Purchase Price],
               
          [Quantity Purchased],
               
          [Quantity Sold],
               
          [Quantity Used],
               
          [Quantity Given],
               
          [Quantity Borrowed],
               
          RangeSum([Quantity Purchased],
                          -
          [Quantity Sold],
                          -
          [Quantity Used],
                          -
          [Quantity Given],
                          -
          [Quantity Borrowed])                    as [Quantity Change]
          FROM
          [Source-Data\QlikView Community\Inventory.xlsx]
          (
          ooxml, embedded labels, table is Inventory);

           

          Mikhail