[SOLVED]App Studio 8201 - need help automating a incremental percent calculation

December 13, 2018, 11:57 AM

Michele Brooks

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,

December 13, 2018, 12:03 PM

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?

December 13, 2018, 12:07 PM

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

December 13, 2018, 12:58 PM

Michele Brooks

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

December 13, 2018, 01:07 PM

Michele Brooks

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