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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name. Connect to myibi
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]App Studio 8201 - need help automating a incremental percent calculation

 Go Search Notify Tools
 [SOLVED]App Studio 8201 - need help automating a incremental percent calculation
Guru
 posted December 13, 2018 11:57 AM
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, 2012 IP
Virtuoso
 posted December 13, 2018 12:03 PM 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, 2015 IP
Guru
 posted December 13, 2018 12:07 PM 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, 2007 IP
Guru
 posted December 13, 2018 12:58 PM 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, 2012 IP
Guru
 posted December 13, 2018 01:07 PM 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, 2012 IP