Count then Average

[mdj3366]

Hi there,
I am using CR 11, SQL database
My database has participants with a payment.payment_date field and payment.status_lkup_id, with the status_lkup_id field assigned a value of 1974 if the payment has been paid, 1975 if the payment is inactive, 2036 if the payment is ineligible and 2037 if the payment is eligible. There is also a field that contains the maximum number of payment dates allowed (benefit duration). I need to get the average of those payments that have been marked as paid (1974) based upon the benefit duration. Any help would be great! Thanks.

 

[Madawc]

SOunds like a Running Total. The use of Crystal's automated totals is outlined at FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[mdj3366]

I created a formula @test to count the number of payments paid:
if {payment.status_lkup_id}=1974 then 1 else 0

I summed the above by group#1 Name on formula @name

I then created a running total on @test, type of summary average, evaluate for each record, reset never. It is returning .45 which i know is incorrect.

 

[lbass]

You would use average of the payment amount, and in the evaluation section use a formula:

{payment.status_lkup_id}=1974 and
{table.benefitduration} = 123 //add your correct field and criterion

Not sure whether you want the result per customer--if so, reset on change of {@name} or if for the whole report, reset never.

-LB

 

[mdj3366]

Hi LB,

I just realized that my report is returning incorrect averages, both in a formula and when using a summary. Do you know what would cause this?

 

[lbass]

Running totals (in most scenarios) are only correct in footer sections. If you have row inflation (repeating values in the detail section), your totals could also be incorrect.

-LB