 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Calculating Outstanding Amount
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Calculating Outstanding Amount Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Calculating Outstanding Amount | Login/Join |
| Platinum Member |
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>, WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS | ||
|
| Virtuoso |
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 Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Platinum Member |
I tried this but it didn't work. WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS | |||
|
| Platinum Member |
Please, any help from someone here? WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS | |||
|
| Expert |
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
| |||||||||
|
| Platinum Member |
Hi Tony, Your suggestion worked. Thank you very much. WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS | |||
|
| Expert |
That's good! However, I would be happier knowing that you understood why it works  and could utilise the logic elsewhere when needed.T
| |||||||||
|
| Platinum Member |
Yes, I got the logic. Thanks once again. WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Calculating Outstanding Amount
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Calculating Outstanding AmountCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |