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.
