Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
kavicsgym
Contributor

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

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

Pic.png

1 Solution

Accepted Solutions
Highlighted
kavicsgym
Contributor

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

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:

Pic2.png

View solution in original post

1 Reply
Highlighted
kavicsgym
Contributor

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

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:

Pic2.png

View solution in original post