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'.
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
@heatherpaquette can you provide the expected output?