cancel
Showing results for 
Search instead for 
Did you mean: 

Avg of DateDiff Formula

cothmer
Participant
0 Kudos

I created a formula to get the minutes between 2 times using DateDiff:

datediff ("n",{PAT_ENC.AVS_PRINT_TM},{PAT_ENC_HSP.HOSP_DISCH_TIME})

This formula is displaying the correct amount in minutes for each patient but my average is way off. In the example below I have 4 patients where the average should be 64.25 but my report says 40.19. I am using a summary to get the average of the DateDiff field. Not sure why it is not giving me the correct average.

Accepted Solutions (0)

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

Are you displaying your data in the details section or is it in a group header or footer? I might try doing a manual average instead. It would look something like this:

if DistinctCount({patient id}, {Group Field}) > 0 then   <-- This prevents a "divide by zero" error
  Sum({@total minutes formula}, {Group Field}) / DistinctCount({patient id}, {Group Field})
else 0

If you're looking for the grand total for the report, leave out ", {Group Field}" in the three places where it's used above.

-Dell

cothmer
Participant
0 Kudos

Data is displayed in a Patient_ID group header called Pat_Enc_Csn_ID) . So would I put the above in a formula ?

Example : if DistinctCount(pat_enc_csn_id (so this would be my patient id ?), {pat_enc_csn_id}) (would this be my group field ?) >0 then

sum({DateDiff} (would this be the DateDiff formula that I wrote?), {pat_enc_csn_id}) / DistinctCount(pat_enc_csn_id}, pat_enc_csn_id})

DellSC
Active Contributor
0 Kudos

If you want the grand total for the report, it will look like this:

if DistinctCount({table.pat_enc_csn_id})>0 then
  Sum({DateDiff Formula})/ DistinctCount({table.pat_enc_csn_id})
else 0

If the patient group is inside a larger group and you want to report on the average for that group instead of for the entire report, then you would use the field that you're grouping outside of the patient id.

-Dell