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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]App Studio 8201 - need help automating a incremental percent calculation

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]App Studio 8201 - need help automating a incremental percent calculation
 Login/Join
 
Guru
posted
I need to automate the following calculation for a report that will run on a schedule in ReportCaster. The report has a column that has charges and a calculated column that shows the days difference between two dates. I need to automate a penalty calculation. The formula for the penalty consists of applying 1% of the charges if the days difference is over 30 days, 2% if the days difference is over 60 days, 3% if the days difference is over 90 days and so on. I ran the first report as an ad hoc and hard coded the penalty percentage. When this report is run in Caster, there is no way of knowing what the days difference will be so I need a calculation that will automatically increment the penalty percentage by 1% for each cumulative 30 day days difference. Pasted below is what I coded in the ad hoc. In this instance, I knew that the ad hoc report results did not exceed 179 days. Thanks.

 
PENALTY/D20.2CM = IF DAYS_DIFF GE 30  AND DAYS_DIFF LE 59  THEN CHARGES * .01 ELSE
                  IF DAYS_DIFF GE 60  AND DAYS_DIFF LE 89  THEN CHARGES * .02 ELSE
		  IF DAYS_DIFF GE 90  AND DAYS_DIFF LE 119 THEN CHARGES * .03 ELSE
		  IF DAYS_DIFF GE 120 AND DAYS_DIFF LE 149 THEN CHARGES * .04 ELSE
		  IF DAYS_DIFF GE 150 AND DAYS_DIFF LE 179 THEN CHARGES * .05 ELSE 0;
 

This message has been edited. Last edited by: Michele Brooks,


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Virtuoso
posted Hide Post
quote:
When this report is run in Caster, there is no way of knowing what the days difference will be so


I don't understand why this is. The age of any record is usually calculated by comparing two dates (usually one of those dates is today's date). How are you calculating DAYS_DIFF?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Guru
posted Hide Post
TABLE FILE CAR
PRINT
COMPUTE DAYS_DIFF /I5= SALES/100;
COMPUTE PENALTYCATEGORY /D12.2 = INT(DAYS_DIFF / 30)/ 100;
COMPUTE PENALTY/D20.2CM = SEATS * PENALTYCATEGORY;
END


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Guru
posted Hide Post
quote:
Originally posted by BabakNYC:
quote:
When this report is run in Caster, there is no way of knowing what the days difference will be so


I don't understand why this is. The age of any record is usually calculated by comparing two dates (usually one of those dates is today's date). How are you calculating DAYS_DIFF?

COMPUTE DAYS_DIFF/D20 = DATEDIF(BILL_DATE, POST_DATE, 'D'); I had to make a slight correction to the calculation that I submitted when I opened up this post. Pasted below is logic that I used for the initial request. I need to determine what penalty % to apply to the calculation based on the days diff without having to hard code the penalty percent.

PENALTY/D20.2CM = IF DAYS_DIFF GT 30 AND DAYS_DIFF LT 60 THEN CHARGES * .01 ELSE
IF DAYS_DIFF GT 60 AND DAYS_DIFF LT 90 THEN CHARGES * .02 ELSE
IF DAYS_DIFF GT 90 AND DAYS_DIFF LT 120 THEN CHARGES * .03 ELSE
IF DAYS_DIFF GT 120 AND DAYS_DIFF LT 150 THEN CHARGES * .04 ELSE
IF DAYS_DIFF GT 150 AND DAYS_DIFF LT 180 THEN CHARGES * .05 ELSE
0;

Sample dump of DAYS_DIFF
Days Diff
41 (since this days diff is greater than 30 and less than 60, .01% penalty needs to be calculated)
42
42
133 (since this days diff is greater than 120 and less than 150, .04% penalty needs to be calculated)
118
120
125
105
110
111
110
112
112
91
99
90
96
105
97




WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Guru
posted Hide Post
Terrific. Thanks so much Frans. This is what I need. I will be closing this case.


quote:
Originally posted by Frans:
TABLE FILE CAR
PRINT
COMPUTE DAYS_DIFF /I5= SALES/100;
COMPUTE PENALTYCATEGORY /D12.2 = INT(DAYS_DIFF / 30)/ 100;
COMPUTE PENALTY/D20.2CM = SEATS * PENALTYCATEGORY;
END


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]App Studio 8201 - need help automating a incremental percent calculation

Copyright © 1996-2020 Information Builders