Averaging in Crystal 8.5 - Wrong Average

[maruja]

Greetings:

I am writing a report for the Cancer Center in a Medical Facility. They have asked to see days stay for patients and at the end of the report they want me to average those days. I have 4 patients. Their days stay are as follows:
1.0
9.0
8.0
6.0
This totals 24 divided by 4 should give me an ALOS of 6.
On my report it gives me 7.37. Can you tell me why? I've looked at formatting, etc. but can't seem to come up with anything. Thanks.

 

[GJParker]

Please show any formulas used in this calculation and the layout of your report groups etc...



Gary Parker
MIS Data Analyst
Manchester, England

 

[maruja]

Gary:

Here is my layout:
GH1: Organization Short Name
GH2: Patient ID, Pt Name, Medical Record No, Admit dt, Discharge Date, Length of Stay, Practioner Name
GH3: Activity Code Description, Activity Total Quantity
Detail: Activity Code Description
GF1: Org Short Name
RF: Grand Total: Number of Patients Average Length
of Stay
There are no calculations. However, here is the SQL from the Crystal report for you perusal. Thanks. Maruja

SELECT
c_cmb_vst_v_20050303."adm_date",
c_cmb_vst_v_20050303."drg_no",
c_cmb_vst_v_20050303."drg_serv_line",
c_cmb_vst_v_20050303."dsch_date",
c_cmb_vst_v_20050303."hosp_svc",
c_cmb_vst_v_20050303."len_of_stay",
c_cmb_vst_v_20050303."med_rec_no",
c_cmb_vst_v_20050303."orgz_short_name",
c_cmb_vst_v_20050303."prim_pyr_cd",
c_cmb_vst_v_20050303."pt_id",
c_cmb_vst_v_20050303."pt_name",
c_cmb_vst_v_20050303."tot_chg_amt",
c_cmb_actv_v_20050303."actv_tot_qty",
c_cmb_actv_v_20050303."actv_cd",
c_cmb_actv_v_20050303."actv_cd_desc",
c_cmb_actv_v_20050303."vst_type_cd",
c_pract_dim_cons_v."pract_name",
c_pract_dim_cons_v."spclty_desc"
FROM
((SMSPHdssp0r0.smsdss.c_cmb_vst_v_20050303 c_cmb_vst_v_20050303 INNER JOIN SMSPHdssp0r0.smsdss.c_cmb_actv_v_20050303 c_cmb_actv_v_20050303 ON
c_cmb_vst_v_20050303."vst_key" = c_cmb_actv_v_20050303."vst_key")
INNER JOIN SMSPHdssp0r0.smsdss.c_smd_consult_v c_smd_consult_v ON
c_cmb_vst_v_20050303."orgz_cd" = c_smd_consult_v."orgz_cd" AND
c_cmb_vst_v_20050303."pt_id" = c_smd_consult_v."pt_id")
INNER JOIN SMSPHdssp0r0.smsdss.c_pract_dim_cons_v c_pract_dim_cons_v ON
c_smd_consult_v."smd_consult_dr_no" = c_pract_dim_cons_v."pract_no"
WHERE
c_cmb_vst_v_20050303."drg_serv_line" <> 'ungroupable' AND
c_cmb_vst_v_20050303."drg_serv_line" <> 'rehab' AND
c_cmb_vst_v_20050303."drg_serv_line" <> 'behavioral health' AND
c_cmb_vst_v_20050303."hosp_svc" <> 'hsp' AND
c_cmb_vst_v_20050303."len_of_stay" <> 0. AND
c_cmb_vst_v_20050303."tot_chg_amt" <> 0. AND
c_cmb_vst_v_20050303."prim_pyr_cd" <> 'j24' AND
c_cmb_vst_v_20050303."prim_pyr_cd" <> 'k01' AND
c_cmb_vst_v_20050303."prim_pyr_cd" <> 'n15' AND
c_cmb_actv_v_20050303."vst_type_cd" = 'I' AND
c_cmb_vst_v_20050303."drg_no" = 82 AND
(c_cmb_actv_v_20050303."actv_cd" = '21701057' OR
c_cmb_actv_v_20050303."actv_cd" = '21700786' OR
c_cmb_actv_v_20050303."actv_cd" = '21700562' OR
c_cmb_actv_v_20050303."actv_cd" = '21700067' OR
c_cmb_actv_v_20050303."actv_cd" = '21700059' OR
c_cmb_actv_v_20050303."actv_cd" = '20431599' OR
c_cmb_actv_v_20050303."actv_cd" = '20431375' OR
c_cmb_actv_v_20050303."actv_cd" = '20431367' OR
c_cmb_actv_v_20050303."actv_cd" = '20431359' OR
c_cmb_actv_v_20050303."actv_cd" = '20431250' OR
c_cmb_actv_v_20050303."actv_cd" = '20431243' OR
c_cmb_actv_v_20050303."actv_cd" = '20431235' OR
c_cmb_actv_v_20050303."actv_cd" = '20431227' OR
c_cmb_actv_v_20050303."actv_cd" = '20431219' OR
c_cmb_actv_v_20050303."actv_cd" = '20431177' OR
c_cmb_actv_v_20050303."actv_cd" = '20431169' OR
c_cmb_actv_v_20050303."actv_cd" = '20431094' OR
c_cmb_actv_v_20050303."actv_cd" = '20431086' OR
c_cmb_actv_v_20050303."actv_cd" = '20431078' OR
c_cmb_actv_v_20050303."actv_cd" = '20431037' OR
c_cmb_actv_v_20050303."actv_cd" = '20431029' OR
c_cmb_actv_v_20050303."actv_cd" = '20431011') AND
c_pract_dim_cons_v."spclty_desc" = 'oncology'
ORDER BY
c_cmb_vst_v_20050303."orgz_short_name" ASC,
c_cmb_vst_v_20050303."pt_id" ASC,
c_cmb_actv_v_20050303."actv_cd_desc" ASC

 

[lbass]

Because of your links, you have the lengths of stay are getting counted multiple times. You might want to just use a variable. Create a formula:

//{@sumlength} to be placed in GH#2:
whileprintingrecords;
numbervar sumlen := sumlen + {c_cmb_vst_v_20050303.len_of_stay};

//{@ave} to be placed in the report footer:
whileprintingrecords;
numbervar sumlen;

sumlen/distinctcount({c_cmb_vst_v_20050303.pt_id})

This assumes that there is one stay per patient per report period. If there were potentially more, then this would have to be adjusted.

-LB