Subtract different delivery quantities from same order quantities

[riverahansiebfusa]

All, 

I have been trying different approaches to address this issue.  I have seen how some used transpose tool and cross tab tool, but this will not work for me due to our data set has thousands of delivery numbers.  Nevertheless, the issue is this.  We have one order, and within the order we have several deliveries, in the case attached we have 4 deliveries.  I need to get a "true order qty" but as you can see in the example, the formula gives the same result over and over, due to I am unable to subtract the delivery from total order.  Remember, in this case to total order was 128, here is showing 128 4 times, but in reality it should show 121 for the first row, the second row should show 114 (because it should calculate 121 - 7) and so on.  Thanks! 

[fmvizcaino · Accepted solution]

Hi @riverahansiebfusa ,

Attached is an example showing how to get the result you want.

It is possible to achieve the same using the multi-row tool as well.

Best,

Fernando Vizcaino

[RolandSchubert]

Hi @riverahansiebfusa ,

what about using a Multi-Row Formula tool? First row has to calcuate "Total Order Qty" - "Delivery Qty", subsequent rows calculate result of previous row - "Delivery Qty". I've attached a sample workflow.

Best

Roland

[riverahansiebfusa]

Thank you, fmvizcaino!  This solution proves to work great. Thanks again!