Subtract a field value from a different row in the same field

[kavicsgym]

Hi All,

I have Customer IDs, Aging groups (0, 1-30, 31-90, etc.) and Amounts (fieldname: Data) in the different age groups. (Accounts Receivables)
I have negative and positive amounts. Neg. amounts are PAYMENTS, Pos. amounts are DEBTS!

I should subtract the Negative amounts from the Positive amount found in the oldest Age category for the same CustID (it is actually Adding, not deducting +10-15). If the Positive amount was less than the Negative, the Remaining Neg amount I should subtract from the Positive Amount found in the next Age category for the same Cust Id. And so on, as long the Sum of Neg amount is fully subtracted.

Thanks a lot! Kavics

[kavicsgym · Accepted solution]

Actually I solved my own problem 😄

I used order bys, Flags and a series of Peek and Previous functions in nested IF statements to look back at previously loaded records and subtract the Payment amounts from Debt amounts (starting from the oldest debt) and gradually using up all the payments.

I am attaching in case anyone needs it.

But if You can suggest a nicer solution with Loops, etc, I am still open for it 🙂

DATA2 is the solution: