Hi,
I will appreciate it of someone here can help with how I can calculate outstanding amount. I have Inv_amt (this is fixed) and Payments from customers per Inv_number. Customer can make multiple payments until the Inv_amt is settled.

I am unable to calculate the Outstanding_amount by substracting payments from reducing Inv_amt.

Below is a sample data:

  
Count	Location	Inv_Number 	Desc	Inv_amt 	Payment		OUTSTANDING_AMOUNT
1	A	1111		TESTA	500	310		190
2	A	1111		TESTA	500	190		0

1	B	2222		TESTB	300	200		100
2	B	2222		TESTB	300	40		60
3	B	2222		TESTB	300  	50		10
 
1	C	3333		TESTC	400	250		150
2	C	3333		TESTC	400	150		0

Just you know, the COUNT column is calculated per payments.

The OUTSTANDING_AMOUNT column is not in the database (table) and I need to calculate this in webfocus.

Please, can someone help with codes/explanation on how I can calculate column OUTSTANDING_AMOUNT?

Many thanks.

This message has been edited. Last edited by: <Kathryn Henning>,

  
TABLE FILE ttt
SUM
MAX.Inv_amt NOPRINT
Payment NOPRINT
COMPUTE OUTSTANDING/I6=MAX.Inv_amt - Payment;
BY Location BY Inv_Number BY Desc
END
 

I tried this but it didn't work.

Please, any help from someone here?

Look at it logically, you want to minus the payment from the outstanding amount invoice by invoice.

So your sort order will need to include invoice number, the first outstanding amount for each invoice amount will be the invoice amount minus the payment for each subsequent payment on that invoice the outstanding amount will be the LAST outstanding amount minus the payment.

Note that the sort order is crucial in getting your calculations correct so you need to think about that.

So you should be able to work out that your COMPUTE would need to be something like -

COMPUTE OUTSTANDING_AMOUNT/D12 = IF Inv_Number EQ LAST Inv_Number THEN LAST OUTSTANDING_AMOUNT - Payment ELSE Inv_Amt - Payment;

T

Hi Tony,
Your suggestion worked. Thank you very much.

That's good! However, I would be happier knowing that you understood why it works and could utilise the logic elsewhere when needed.

T

Yes, I got the logic. Thanks once again.