0 Replies Latest reply: Mar 28, 2018 4:30 AM by Aviad Bein RSS

    Inventory Tracking & Monitoring - Pivot Table

    Aviad Bein

      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