Formula using data from various columns

Question

Hello,

I am trying to create a formula to show two new columns calculating the difference between one data criteria using multiple other data criteria.  For example, I would like to add two columns, for 'Intransit' and 'Prior Month Intransit' which calculate Order Batch 19027661, schedule type DISB, billed gallons total minus Order Batch 19027661, schedule type RECP billed gallons.  For this example the difference is zero, but for order batch 23088756, there is no RECP so the difference is 31,281.  If the calculation of receipts minus disbursements is positive, the 'Intransit' and if negative 'Prior Month Intransit'.

Intransit Test.xlsx

binu_acs · Answer

@heatherpaquette can you provide the expected output?

griffinwelsh · Answer

If I understood correctly this should work for you.

1.Assign a negative value to the RECP rows.

2.Use summarize to group by your criteria and get sum.

3. If the sum is negative assign to one output column

4. if the sum is positive assign to other output column

5. rejoin to original dataset

Solution is attached

Solution.yxmd