Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Inventory Tracking & Monitoring - Pivot Table

Hi all,

In order to track inventory level and behavior I would like to generate the following Pivot Table:

Rows: PartNumber, Measures

Columns: Month(DueDate)


Open Inventory: Close Inventory of previous month

Incoming:  sum(PurchaseOrders)

Outgoing: -sum(SalesOrders)

Close Inventory: =sum(PurchaseOrders) - sum(SalesOrders)

In a nutshell, I would like to calculate open and close inventory per month for each PartNumber.

The expected result should be something like this:

Part NumberMarAprMayJulJul
PN1Open InventoryMar2040-1010
Close Inventory2040-101060
PN2Open Inventory2060140140
Close Inventory206014014040
PN#Open Inventory102030-10
Close Inventory102030-1030

I managed to generate it with Table (without "per Part Number") by using the following logic (and formulas):

Open Inventory: Rangesum(above(total Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'}>}Quantity),1,rowno(total)))

Incoming: Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'},OrderType = {'Purchase Orders'}>}Quantity)

Outgoing: Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'},OrderType = {'Sales Orders'}>}Quantity)

Close Inventory: Rangesum(above(total Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'}>}Quantity),0,rowno(total)))

BUT, as long as Rangesum(above()) function works fine with Table it's not working with Pivot Table as I expected (not calculating each Part Number separately).

What am I doing wrong?

Thanks in advance

0 Replies