Max(charge_date, amount) sorted by month 1

[jphillips]

Have a report that is going to be used to calculate a deposit amount for a customer. The rules are:
State 1
1. The highest bill for last year = deposit..

State 2
2. The sum(two highest bills)*2.5 last 12 months.

The reports selects 12 months of billing, sums it up for each month and gives me a total at the bottom of the report.

What I need is a formula that will look at each months bills and select the highest amount or the two highest bills added together, so I can do calculates off that amount.
Any idea's
Jon

 

[SmpTraining]

I think you will need to use variables for this. Something like this should work.

Create a formula:

@Reset_vars;

WhilePrintingRecords;
NumberVar High1:=0;
NumberVar High2:=0;

Insert this in the report where the values need to be initialised. Probably in the Customer Group Header.

Then create another formula:

@Update_vars;
WhilePrintingRecords;
NumberVar High1;
NumberVar High2;
// table.amount represents your monthly bill field
if table.amount > High1 then
   // We've found our highest amount so far
   High2 := High1
   High1 := table.amount
else
   // Its not the highest but is it second highest?
   if table.amount > High2 then
      // Yes - it is
      High2 := table.amount

Insert this formula in the details section (I'm assuming the details section repeats once for each monthly bill)

You will be able to use the variables High1 and High2 in your other formulas - just make sure you use WhilePrintingRecords!

If you want to display the values on the report, include the following formulas:

@Show_High1;
WhilePrintingRecords;
NumberVar High1;

@Show_High2;
WhilePrintingRecords;
NumberVar High2;

Steve Phillips, Crystal Consultant

 

[jphillips]

Steve,

Thanks for replying. I created all the formulas and used the correct fields, but I run into an error stating
'The remaining text does not seem to be part of the formula'
the cursor is at * below

High2 := High1
*High1 := table.amount

any ideas
JP

 

[SmpTraining]

Sorry for the delay I didn't set the email notification ON!

The problem is that when using Crystal syntax for formulas you cannot have multiple statements between an if..then..else statement. But there is a way around this.

Inserting brackets around the multiple statements fixes the problem. The affected code should look like this:

if table.amount > High1 then
   // We've found our highest amount so far
   ( High2 := High1; 
     High1 := table.amount )
else

Hope this response has not come too late for you. Steve Phillips, Crystal Consultant

 

[jphillips]

Steve,

Worked like a charm. Thanks so much for your help. I ended up having to put the reset_vars in the report header.
Here is the final code, hope this helps someone else.
******UPDATE_VARS*****

WhilePrintingRecords;
NumberVar High1;
NumberVar High2;
// table.amount represents your monthly bill field
if Sum ({BILLED_CHG}, {CHARGE_DATE}, "monthly&quot > High1 then
// We've found our highest amount so far
(High2 := High1;
High1 := Sum ({BILLED_CHG}, {CHARGE_DATE}, "monthly&quot) else
// Its not the highest but is it second highest?
if Sum ({BILLED_CHG}, {CHARGE_DATE}, "monthly&quot > High2 then
// Yes - it is
High2 := Sum ({BILLED_CHG}, {CHARGE_DATE}, "monthly&quot


Thanks again.
JP