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)

      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