4 Replies Latest reply: Dec 18, 2015 1:42 PM by Ben VanderLaan RSS

    How to sum 2 fields into 1 column while summing values from each column into 1 row?

    Ben VanderLaan

      Tough way to ask this, but I'll use pictures to try and explain. I'm getting the correct values for Onhand Qty, Prod Qty, and Ordered Qty (these are just taking the sum of each field ex: Sum({< LinkId = {'INVHST'} >} [Inventory Free Quantity])  --> gives me Onhand Qty)...

      QV screenshot4.PNG

      ...but I'm getting the wrong value for Last Receipt Quantity. I want to take the sum of two fields (inventory free quantity and inventory reserved quantity), but only use the values that are associated with the last receipt date. So for example: the database that we are pulling the values from has a [Inventory Free Quantity] of 0 on 12/15/15, while having an [Inventory Reserved Quantity] of 3,000 on that same date, but on 7/31/15 [Inventory Free Quantity] has a value of 8,100 and [Inventory Reserved Quantity] has a value of 0. The formula I'm using for that column is: Sum({<[Inventory Date] = {"<=$(=date(max([Inventory Date])))"} >} [Inventory Free Quantity] + [Inventory Reserved Quantity]). However, this just takes the sum of both columns because both have a max date (giving me 8,100 + 3,000).

       

      So when I add [Inventory Date] as a dimension I get this..

      QV screenshot2.PNG

      This gives me the correct value for Last Receipt Quantity for the row on '12/15/2015', but what I want is all of the values to be on 1 row.

       

      So my desired result would be: Onhand Qty = 8,100, Prod Qty = 3,000, Ordered Qty = 10,200, and Last Receipt Quantity = 3,000.

       

      I'm not sure if this can be done, but I'd appreciate any help!