1 Reply Latest reply: Feb 12, 2018 5:06 PM by Jahanzeb Hashmi RSS

    Return child order totals when parent total is selected

    Justin Dallas

      Hello Everyone,

       

      I think I've got a P() problem that I can't seem to fight my way through.  In my data model, we have Orders, and they can contain child Orders.  This relationship is maintained in a field called "parentOrder" which contains the id of the parent order.  I have a pivot table, where I would like to show the summing data concerning an Order, as well as the summing data of it's children too.

       

      A simple data model with parity would look like this:

      Orders:
      LOAD * Inline
      [
      orderId , parentOrder
          1 ,
          2 , 1
      ]
      ;
      
      Invoices:
      LOAD * Inline
      [
      orderId , invoiceTotal
          1 , 100
          2 , 50
      ]
      ;
      
      
      
      

       

       

      In my pivot table, I would have a row that would look like this

       

      Order#    |    Invoice Sum | Child Order Invoice Sum

      1              |              100      |          50

       

       

      How I've embarrassed myself What I've tried:

       

      The closest I've gotten is the following statement.

       

      SUM({1<[Parent Order Number] = P({$<[Order Number]>}[Order Number])>} [Charge])
      

       

      Here, I'm trying to say,

       

      Sum the charges over the Set Where the Parent Order Number is equivalent to the possible
      Order Numbers in the current selection.  Because the [Order Number, Parent Order Number] 
      may not be in the current selection ($) ignore what the user has selected (1)
      

       

      This kind of works, but when I put it into a KPI component, I don't see my Sum changing with the selection changes.

       

      Any help is greatly appreciated.