Focal Point Banner


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
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Calculating Totals
 Login/Join
 
Platinum Member
posted
I have a pledge reminder that has installments. I want to display the schedule of installments along with the amount paid beside each installment. How do I keep the running total to manipulate each installment that was paid. Final result will look like this:
Inst Date Inst Amt Amt Paid
6/30/2004 10.00 10.00
6/30/2005 15.00 15.00
6/30/2006 25.00 0.00
6/30/2007 35.00 0.00
6/30/2008 45.00 0.00

So the total paid was 25 which I have a field for. I have a field for the installment amount also. I just have to calculate the amt paid for each installment based on how much was already paid


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
I might be missing something since this seems pretty straightforward. Are you just wanting a running total of amount paid? Something like this:

TABLE FILE CAR
PRINT
CAR
RCOST
COMPUTE RUN_TOT=RUN_TOT + RCOST;
BY COUNTRY
END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
Say a person has equal installments of 1000 each for 5 years. They are past due for the past 3 years, but has only paid 1500 total. The report will show the 5 installments, with the amount of each installment that was paid beside each, when it gets to the second installment it would say 500 beside it instead of 1000. And the rest would say 0.

Date INST AMT AMT PAID
6/6/03 1000 1000
6/6/04 1000 500
6/6/05 1000 0
6/6/06 1000 0
6/6/07 1000 0

Amt Due Now is $2500
I just want to know how to display the amt paid since its not a real field in my database.


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Platinum Member
posted Hide Post
And I know to display that total correctly I would have to compare the installment with the total amount paid, then decrement the amount paid by the installment if the installment is less than the amt paid. But when I go to the next installment, I get confused on how to hold that decremented amt paid value. So by the time I get to 0 for the amt paid I would know to display zeros for the installments left.


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
the question is for me a bit confusing, but would you be able to do this in excel?
can you work that out and display the results here?
that might be more desciptive.
Maybe you need FML here to get this done??

Frank




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
It's just a matter of figuring out the logic. This example should give you what you need. The amt_pd and inst_due columns are what you're going to display. The others are just so you can see how the logic is working.

DEFINE FILE CAR
DCOST1/D15.2C=5000;
END
TABLE FILE CAR
PRINT
CAR
RCOST AS INSTALLMENT
DCOST1 AS PAID
COMPUTE TOT_DUE_RUN/D15.2C=TOT_DUE_RUN + RCOST;
COMPUTE TOT_PD_RUN/D15.2C=TOT_PD_RUN + DCOST1;
COMPUTE AMT_PD/D15.2C=IF TOT.DCOST1 GT TOT_DUE_RUN THEN RCOST ELSE
IF TOT_DUE_RUN - TOT.DCOST1 LT RCOST THEN TOT_DUE_RUN - TOT.DCOST1 ELSE 0;
COMPUTE INST_DUE=RCOST - AMT_PD;
COMPUTE TOT_UNPD_RUN/D15.2C=TOT_UNPD_RUN + INST_DUE;
BY COUNTRY
END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Guru
posted Hide Post
 
Inst Date Inst Amt Amt Paid

6/30/2004 10.00    10.00
6/30/2005 15.00    15.00
6/30/2006 25.00     0.00
6/30/2007 35.00     0.00
6/30/2008 45.00     0.00 



I'm still trying to understand what it is that you have.

For each date indicated do you have the pledge amount and the (running) total paid to that date? Then, you calculate the amount paid at the date of each pledged installment?

 
Inst Date Inst Amt Total Paid Amt Paid

6/30/2004 10.00    10.00      10.00
6/30/2005 15.00    25.00      15.00
6/30/2006 25.00    25.00       0.00
6/30/2007 35.00    25.00       0.00
6/30/2008 45.00    25.00       0.00 


I'm assuming you have the first three fields in your db and are calculating the last one. Is that correct? What does your master file look like?

To calculate the last column you'd need to consider the sorting and have something like:

COMPUTE INST_TOT/F8.2 = IF LAST Pledge_ID EQ Pledge_ID THEN INST_TOT + Inst_amount
ELSE Inst_amount;
COMPUTE Amount_Paid/F8.2 = IF LAST Pledge_ID EQ Pledge_ID THEN
(IF (INST_TOT - Total_Paid) LT 0 THEN 0 ELSE INST_TOT - Total_Paid) ELSE
Inst_amount - Total_Paid;


BY Pledge_ID
BY Inst_date


I hope that makes sense.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Platinum Member
posted Hide Post
I am just getting back from Vegas (Banner Summit) . I will try these solutions this weekend if I get a chance. But yes, I am trying to calculate the last column. Just by looking at the code and the extra column you added, it makes sense....can't wait to try...stay tuned....


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
Tracie,

Here is another contribution. I created a MASTER and some data to represent your problem:
MASTER
FILENAME=TRACIE, SUFFIX=FIX,
  DATASET=C:\IBI\APPS\FOCALPOINT\TRACIE.DAT
 SEGNAME=CUST, SEGTYPE=S0
  FIELDNAME=CUSTNO, FORMAT=I4, ACTUAL=A4, $
  FIELDNAME=PAID_AMT, FORMAT=D9.2, ACTUAL=A10, $
 SEGNAME=INST, PARENT=CUST, OCCURS=VARIABLE
  FIELDNAME=IDATE, FORMAT=MDYY, ACTUAL=A10, $
  FIELDNAME=I_AMT, FORMAT=D6.2, ACTUAL=A6, $
  FIELDNAME=I_NUM, ALIAS=ORDER, FORMAT=I3, ACTUAL=I4, $

DATA
1234 000025.00 6/30/2004 10.00 6/30/2005 15.00 6/30/2006 25.00 6/30/2007 35.00 6/30/2008 45.00 
1235 000040.00 6/30/2005 15.00 6/30/2006 25.00 6/30/2007 35.00 
1236 000075.00 6/30/2003 10.00 6/30/2004 10.00 6/30/2005 15.00 6/30/2006 25.00 6/30/2007 35.00 6/30/2008 45.00 



The program follows:
TABLE FILE TRACIE
PRINT 
I_AMT PAID_AMT NOPRINT 
COMPUTE T_AMT/D9.2=IF CUSTNO EQ LAST CUSTNO THEN LAST T_AMT + I_AMT ELSE I_AMT; NOPRINT
COMPUTE D_AMT/D9.2=PAID_AMT - T_AMT; NOPRINT
COMPUTE R_AMT/D9.2=IF D_AMT GE 0 THEN I_AMT ELSE IF LAST D_AMT GT 0 THEN LAST D_AMT ELSE 0; 
BY CUSTNO SUBTOTAL 
BY PAID_AMT
BY IDATE
ON TABLE NOTOTAL
END

Good luck!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
Thanks Danny! I ended up using yours but I had to add an if statement to the last compute because it didn't account for a partial payment of the 1st installment. So if someone paid 1500 on a 3000 installment and that is all they paid, the first amt paid is 1500, yours was giving me 0. The rest worked perfectly! Thanks so much!


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Report This Post
Virtuoso
posted Hide Post
Tracie,
You are most welcome.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders