Adding and divding numbers in Group footer

[azoe]

Bottom line: In group footer 2 I want to do some addition and division on the totals. Getting the info from a stored proc (union query one side getting charges, the other getting payments and adjustments) & using Crystal 11.

Detail:
I have two groups:

The first is by Year and the second is by Month.

Then in group footer 2 (by Month) I show 3 totals: Charges, Payments, and Adjustments. The charge detail may be a zero so I have to check for that to avoid division by zero. Detail is suppressed.

I need to do 2 more formulas and that is where I am stuck. They want a 3 month average Net and Gross. First I’m trying to tackle the Net and Gross, then I’ll try the 3 month rolling average.

Net is Payments plus Adjustments divided by charges.
Gross is payments divided by charges.
Sometimes charges are zero (individual records may be zero, not the group footer total).

Here’s the formula so far which doesn’t include the 3 month part.
//@3moNetAverage
({@PaymentNotNull} + {@AdjustmentNotNull}) / {@ChargeNotNull}

The other formulas:
//@PaymentNotNull
If isnull({aa_dch_PercentOfGrossCollections;1.PaidAmt})
Then 0
else {aa_dch_PercentOfGrossCollections;1.PaidAmt}

//@AdjustmentNotNull
If isnull({aa_dch_PercentOfGrossCollections;1.AdjAmt})
Then 0
else {aa_dch_PercentOfGrossCollections;1.AdjAmt}

//@ChargeNotNull
If isnull({aa_dch_PercentOfGrossCollections;1.ChgAmt}) or {aa_dch_PercentOfGrossCollections;1.ChgAmt} = 0
Then 1 //**I did this to avoid division by zero but maybe that's not right - when I make it zero I still get the division by zero error.
Else {aa_dch_PercentOfGrossCollections;1.ChgAmt}

I also tried this:
//@3moNetAverage
({@PaymentNotNull} + {@AdjustmentNotNull}) / {@ChargeNotZero}

with @ChargeNotZero being:
whileprintingrecords;
numbervar Chg;

if {@HasCharge} > 0 then
Chg := Chg + {aa_dch_PercentOfGrossCollections;1.ChgAmt};
But I get: a summary has been specified on a non-recurring field.

Making the ChgAmt 1 if it is zero doesn't work because I'm getting payments plus adjustments but its not divided by ChgAmt.

There are 2 parameters in the report. A start date and an end date.
After the calculation above is working, somehow I need to look at the parameter and if they have picked January through May I can show nothing for January and February but on the March row I’ll need to show the average net (and average gross) of January, February and March. Then on the April row I'll show the same for Feb, Mar and Apr. And so on.

But for now, how do I add group footer 2 total payments and adjustments divided by charges?

A sample of Groups and data:
Chgs Pmts Adj NetColl GrossColl
2006
January
100,150.00 20,000.00 500.00 0.204 0.199
2005
December
200,300.00 150,000.00 300.00 0.750 0.748

Hope that's not too much or too little detail.
Thank you.

 

[azoe]

oops - I think I found it:

ABS(Sum ({aa_dch_PercentOfGrossCollections;1.PaidAmt}, MonthsGroupFooter2}) % ({RunningTotalCharges}) )

Any ideas on how to do the rolling 3 month average?

Thank you.