Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi Sir
can you pls share with me your inventory table model ? As I need to do this.
Paul