Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
aviadbein
New 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)

Measures:

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
Incoming80600100100
Outgoing-60-40-50-80-50
Close Inventory2040-101060
PN2Open Inventory2060140140
Incoming100100100100100
Outgoing-80-60-20-100-200
Close Inventory206014014040
PN#Open Inventory102030-10
Incoming505050080
Outgoing-40-40-40-40-40
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

Tags (2)