Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]App Studio 8201 - need help automating a incremental percent calculation
 Login/Join
 
Platinum Member
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 8201, Windows 7
Oracle DBMS
EXL07/PDF Output
 
Posts: 242 | Registered: August 27, 2012Reply With QuoteReport 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 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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: 381 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Platinum Member
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 8201, Windows 7
Oracle DBMS
EXL07/PDF Output
 
Posts: 242 | Registered: August 27, 2012Reply With QuoteReport This Post
Platinum Member
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 8201, Windows 7
Oracle DBMS
EXL07/PDF Output
 
Posts: 242 | Registered: August 27, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.