Average age of a group by unique ID

[Meredith]

I'm trying to calculate an age average and weed out duplicates in the formula but I'm not having much luck. I've created a medical report that includes a patient ID (which is unique to that particular patient) and their age. A particular patient may show up a few times on the report and I only want to count their age once in the formula. I'm also grouping the patients by what site (hospital) they were admitted to.

In short, I basically need average age by unique patient ID.

Any help is very appreciated,
Meredith

 

[Naith]

Like Sum({Age},{Site}) / DistinctCount({PatientID},{Site})?

That gets you the average age per hospital. I'm not sure what you'd do with "the average age by unique patient ID", because the average age by patient is surely going to be the patient's actual age, isn't it?

Naith

 

[Meredith]

I do want the average age per hospital. The only problem is that within the group itself, I cannot factor the same patient in the calculation twice. Patients may show up multiple times for various reasons, like multiple diagnoses.

For example:
Site 1000
ID: 30303 Name: Jimmy Age: 39
ID: 30304 Name: Sarah Age: 30
ID: 30305 Name: Lucy Age: 30
ID: 30303 Name: Jimmy Age: 39

Average age: 33

Basically it should ignore all repeated patients (those with the same IDs). It should not exclude ages that are duplicated because I may have two unique patients with the same age.

 

[Meredith]

I do want the average age per hospital. The only problem is that within the group itself, I cannot factor the same patient in the calculation twice. Patients may show up multiple times for various reasons, like multiple diagnoses.

For example:
Site 1000
ID: 30303 Name: Jimmy Age: 39
ID: 30304 Name: Sarah Age: 30
ID: 30305 Name: Lucy Age: 30
ID: 30303 Name: Jimmy Age: 39

Average age: 33

Basically it should ignore all repeated patients (those with the same IDs). It should not exclude ages that are duplicated because I may have two unique patients with the same age.

 

[Naith]

I would suggest that you order the patients by PatientID, and use a conditional running total to accumulate the age. You can do this using Crystal's own RT, or create your own variables.

//Get the Total Age of the Group
WhilePrintingRecords;
NumberVar GroupAge;

If Not OnFirstRecord Then
	If Previous({PatientID}) <> {PatientID}
	Then GroupAge := GroupAge + {Age}
	Else GroupAge
Else GroupAge;

//Average it Out
GroupAge / DistinctCount({PatientID})

//Reset the Group Age Total (Place in Group Header)
WhilePrintingRecords;
GroupAge := 0;

I haven't tested the above code, but I think it should be fine. Like I say, you can alternatively use the RT functions and apply your own conditions.

All the best,

Naith

 

[Naith]

//Average it Out
GroupAge / DistinctCount({PatientID})

should be

//Average it Out
GroupAge / DistinctCount({PatientID},{Site})

Sorry about that.

Naith