vgriffin0423

asked on

# Help with calculating average

I have a formula field in the Details section of a Crystal Report. The report runs fine. Now, however, I would like to calculate the AVERAGE of that formula field. Crystal Reports does not have the Insert>Summary option for formulas. Does anyone know how to calculate an average for a formula field? I have attached a copy of my report. I want to get the average on the turnover time for this doctor

turnoverorthopedics.pdf

turnoverorthopedics.pdf

My guess is that you are computing the time in and time out from two different records and Previous() or Next() function. Once you apply a Previous() function, the formula becomes PrintTime formula and can't be summed.

You have two choices:

1. Use variables to accumulate the total and number of "visits" to compute a manual average.

2. Join the "In" records to an aliased copy of the same table, providing the "out" records.

This would allow you to compute the difference inside what the reports see as a single detail record. You can then use a simple average.

I think solution #2 is "cleaner" and can provide other advantages as well.

You have two choices:

1. Use variables to accumulate the total and number of "visits" to compute a manual average.

2. Join the "In" records to an aliased copy of the same table, providing the "out" records.

This would allow you to compute the difference inside what the reports see as a single detail record. You can then use a simple average.

I think solution #2 is "cleaner" and can provide other advantages as well.

ASKER

Thank you for your help. Do you mind showing me an example of what you would do for #2 please

this is how I'm getting the turnover time:

if OnfirstRecord then

0

Else if Date({v_basic_case_rec_data.cr_prdate}) <> Date(Previous({v_basic_case_rec_data.cr_prdate}))then

0

Else if {v_basic_case_rec_data.room_mnc} <> Previous({v_basic_case_rec_data.room_mnc}) then

0

Else

if {v_basic_case_rec_data.surg_descr} <> previous({v_basic_case_rec_data.surg_descr}) then

0

else

IF {v_basic_case_rec_data.room_mnc} = PREVIOUS ({v_basic_case_rec_data.room_mnc})

THEN ({@start} - PREVIOUS ({@end}))/60;

the formula name is @timediff. when I try to average this I can't becausse of the "previous", right?

this is how I'm getting the turnover time:

if OnfirstRecord then

0

Else if Date({v_basic_case_rec_dat

0

Else if {v_basic_case_rec_data.roo

0

Else

if {v_basic_case_rec_data.sur

0

else

IF {v_basic_case_rec_data.roo

THEN ({@start} - PREVIOUS ({@end}))/60;

the formula name is @timediff. when I try to average this I can't becausse of the "previous", right?

Add v_basic_case_rec_data a second time to the report.

Join on

cr_prdate = cr_prdate

room_mnc = room_mnc

surg_descr = surg_descr

Patient = Patient

and the DateTime_column < DateTime_column

Join on

cr_prdate = cr_prdate

room_mnc = room_mnc

surg_descr = surg_descr

Patient = Patient

and the DateTime_column < DateTime_column

ASKER

I've added the alias table and joined on the fields mentioned above. except the "datetime column (wasn't sure what you meant by that) . I'm still not able to do the average on the Timediff formula. What am I missing? Timediff formula is a number datatype.

links.docx

TurnoverOrthopedics.rpt

links.docx

TurnoverOrthopedics.rpt

ASKER

I have been looking into other posts that have calculated the average and I see that there have been suggestion to use the formulas below: would something like this work for me. I tried doing this using my @timediff field but I go an error back saying "this field cannot be summarized"

In the Report header:

WhilePrintingRecords;

Global NumberVar myTotal;

myTotal

In the Details section:

WHilePrintingRecords;

Global NumberVar myTotal;

Local NUmberVar myAverage;

myAverage := sum({@timediff})/

count{v_basic_case_rec_data.surg_descr}

myTotal := myTotal+ myAverage ;

myAverage

In Group Footer:

WhilePrintingRecords;

Global NumberVar myTotal;

myTotal

In the Report header:

WhilePrintingRecords;

Global NumberVar myTotal;

myTotal

In the Details section:

WHilePrintingRecords;

Global NumberVar myTotal;

Local NUmberVar myAverage;

myAverage := sum({@timediff})/

count{v_basic_case_rec_dat

myTotal := myTotal+ myAverage ;

myAverage

In Group Footer:

WhilePrintingRecords;

Global NumberVar myTotal;

myTotal

The average is not necessarily the sum of the averages.

I changed the timediff2 and average formulas

mlmcc

TurnoverOrthopedicsrev1.rpt

I changed the timediff2 and average formulas

mlmcc

TurnoverOrthopedicsrev1.rpt

ASKER

Thanks mlmcc, i put the average forumla in the group footer but the calculations are not correct. What am I doing wrong?

I would need to see the data to figure that out.

mlmcc

mlmcc

ASKER

I placed the average formula in the report footer and seems to be working. I am going to work on the calculation without the zeros. If I have a report with multiple doctors (see attached report) how do I separate out the average time for each doctor?

TurnoverOrthopedicsAllDocs.rpt

TurnoverOrthopedicsAllDocs.rpt

You need to group on the doctors

You can then do a similar set of formulas. TYhe only change is to reset the total and count to 0 in the doctor group header.

mlmcc

You can then do a similar set of formulas. TYhe only change is to reset the total and count to 0 in the doctor group header.

mlmcc

ASKER

I'm sorry I don't understand what you mean by "reset the total and count to 0 in the doctor group header." I've grouped on the doctors and I've attempted to create the formulas.

TurnoverOpthalmology.rpt

TurnoverOpthalmology.rpt

Was that the correct report? You said that you had grouped on the doctors, but the only groups in that report are on cr_prdate and room_mnc, which I assume are not the doctor. And I don't see a formula like the timediff2 formula in the report that mlmcc posted.

As for resetting the total and count, if you look at the timediff2 formula in mlmcc's report, it updates variables named TotalHours and WorkOrderCount. Those variables would then be used in another formula to calculate the average. mlmcc used them in your avgrage formula (Should that be "average"?), although he didn't actually put that formula on the report.

If you want a separate average for each doctor (in addition to the average that's already being calculated), you would create similar formulas that use new variables for the total and count for a doctor. You would also need to create another formula that reset those variables to 0 for each new doctor (similar to the {@grp footer1} formula in mlmcc's report), and put that formula in the doctor group header or footer (where you want it may depend on where your other formulas are located).

Also, FWIW, I think your start and end formulas could be much simpler. It looks like your time_pat_in_room and time_pat_out_of_room fields are just times in number form. For example, 9:10 AM would be 910 and 11:05 PM would be 2,305. If so, you could just use this:

//start

TimeValue (Picture (CStr ({v_CRA_01_OR_Start_Times_Personnel.time_pat_in_room}, "0000"), "xx:xx"))

//end

TimeValue (Picture (CStr ({v_CRA_12_OR_End_Times.time_pat_out_of_room}, "0000"), "xx:xx"))

CStr with the "0000" format converts the number to a 4 character string, with leading 0's if necessary (eg. "0910" and "2305"), and the Picture function inserts a ":" in the middle (eg. "09:10" and "23:05"), and then that's converted to a time.

I don't see anything wrong with what you're doing. It could just be simpler (and, I think, easier to read and follow).

James

As for resetting the total and count, if you look at the timediff2 formula in mlmcc's report, it updates variables named TotalHours and WorkOrderCount. Those variables would then be used in another formula to calculate the average. mlmcc used them in your avgrage formula (Should that be "average"?), although he didn't actually put that formula on the report.

If you want a separate average for each doctor (in addition to the average that's already being calculated), you would create similar formulas that use new variables for the total and count for a doctor. You would also need to create another formula that reset those variables to 0 for each new doctor (similar to the {@grp footer1} formula in mlmcc's report), and put that formula in the doctor group header or footer (where you want it may depend on where your other formulas are located).

Also, FWIW, I think your start and end formulas could be much simpler. It looks like your time_pat_in_room and time_pat_out_of_room fields are just times in number form. For example, 9:10 AM would be 910 and 11:05 PM would be 2,305. If so, you could just use this:

//start

TimeValue (Picture (CStr ({v_CRA_01_OR_Start_Times_

//end

TimeValue (Picture (CStr ({v_CRA_12_OR_End_Times.ti

CStr with the "0000" format converts the number to a 4 character string, with leading 0's if necessary (eg. "0910" and "2305"), and the Picture function inserts a ":" in the middle (eg. "09:10" and "23:05"), and then that's converted to a time.

I don't see anything wrong with what you're doing. It could just be simpler (and, I think, easier to read and follow).

James

ASKER

Thanks James

That wasn't the correct report (the correct one is attached) I am getting Division by Zero errors when I try to create my formulas....................

TurnoverOrthopedicsrev2.rpt

That wasn't the correct report (the correct one is attached) I am getting Division by Zero errors when I try to create my formulas..................

TurnoverOrthopedicsrev2.rpt

The way to fix a divide by 0 is to test for it fir

If A = 0 then

0

Else

B / A

mlmcc

TurnoverOrthopedicsrev3.rpt

If A = 0 then

0

Else

B / A

mlmcc

TurnoverOrthopedicsrev3.rpt

ASKER

Thanks mlmcc,

Should I be adding something else to the formulas that you fixed? Putting them on the report only returns 0.00. I understand what James said for me to do, but I don't where to put them on the report to make them return a number.

Should I be adding something else to the formulas that you fixed? Putting them on the report only returns 0.00. I understand what James said for me to do, but I don't where to put them on the report to make them return a number.

You aren't counting the workorders or summing the time anywhere, at least not with the variables used in the average calculation

mlmcc

TurnoverOrthopedicsrev3-1-.rpt

mlmcc

TurnoverOrthopedicsrev3-1-.rpt

ASKER

So I've decided to create separate reports for each doctor. I'm finding that the averages are wrong because of the records with zero. how are they eliminated? This will be my last question for this post.............................

GushchinTurnover.rpt

GushchinTurnover.rpt

ASKER CERTIFIED SOLUTION

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

ASKER

Turnover time is based on patient out to next patient in. The records with 0 are either first cases or room with only a single case. They are the ones that I want eliminated so that I can get a correct average.

I tried the forumla If {YourField} <> 0 then

(

Add and count

);

keeps coming back with missing ) .

I tried the forumla If {YourField} <> 0 then

(

Add and count

);

keeps coming back with missing ) .

In the last report that you posted, {@time diff} is sometimes 0 (on the first record, when the date has changed, etc.). And {@timediff2} also uses 0 instead of {@time diff} when it's over 120. If you don't want the average to include any record where {@time diff} is 0 or > 120, maybe something like this:

///this gets rid of times > 120 minutes

WhilePrintingRecords;

Global NumberVar TotalHours;

Global NumberVar WorkOrderCount;

Local NumberVar currentdiff;

if {@time diff} > 120 or {@time diff} = 0 then

currentdiff := 0

else

(

currentdiff := {@time diff};

TotalHours := TotalHours + currentdiff;

WorkOrderCount := WorkOrderCount + 1

);

currentdiff

The key difference is that WorkOrderCount is only incremented when {@time diff} is <> 0 and <= 120.

James

///this gets rid of times > 120 minutes

WhilePrintingRecords;

Global NumberVar TotalHours;

Global NumberVar WorkOrderCount;

Local NumberVar currentdiff;

if {@time diff} > 120 or {@time diff} = 0 then

currentdiff := 0

else

(

currentdiff := {@time diff};

TotalHours := TotalHours + currentdiff;

WorkOrderCount := WorkOrderCount + 1

);

currentdiff

The key difference is that WorkOrderCount is only incremented when {@time diff} is <> 0 and <= 120.

James

ASKER

I tried that and it still gives me the records with zeros

GushchinTurnover-1-.rpt

GushchinTurnover-1-.rpt

ASKER

Thank you mlmcc and James for your continued help with this report. You are truly professional experts, and I appreciate your time.

THANK YOU!!!!

THANK YOU!!!!

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for vgriffin0423's comment #a38740811

Assisted answer: 125 points for James0628's comment #a38728882

Assisted answer: 125 points for mlmcc's comment #a38732791

Assisted answer: 125 points for mlmcc's comment #a38738924

Assisted answer: 125 points for James0628's comment #a38740312

for the following reason:

.

Accepted answer: 0 points for vgriffin0423's comment #a38740811

Assisted answer: 125 points for James0628's comment #a38728882

Assisted answer: 125 points for mlmcc's comment #a38732791

Assisted answer: 125 points for mlmcc's comment #a38738924

Assisted answer: 125 points for James0628's comment #a38740312

for the following reason:

.

Did you get it to work then? Just making sure.

James

James

ASKER

I'm still having some issues with this report, any chance I can re-open this? I can't figure out how to calculate the averages for multiple doctors. I know I stated that I was going to do a separate report for each doctor, but my customers would like to see all of the doctors for a particular service. So far I have the two formulas that James and mlmcc asssisted me with. I've tried adding two additional formulas to the report header and the group footer. When I do that I get a division by zero error in the average formula. I have attached the report.

TurnoverOrthopedics.rpt

TurnoverOrthopedics.rpt

WHy are you setting the counts and totals to 0 in the group footer?

mlmcc

mlmcc

What are TotalHours and WorkOrderCount? Are they supposed to be a total and count for each individual doctor (surg_descr), or for the whole report?

If they're supposed to be for each doctor, then setting them to 0 in GH1 or GF1 would make sense, to reset them for each new doctor. But then your @avgrage formula should also be in a GF1 section, to show the average for each doctor.

If TotalHours and WorkOrderCount are supposed to be the total and count for the entire report, then they should never be reset to 0. In that case, remove the {@grp footer1} formula.

James

If they're supposed to be for each doctor, then setting them to 0 in GH1 or GF1 would make sense, to reset them for each new doctor. But then your @avgrage formula should also be in a GF1 section, to show the average for each doctor.

If TotalHours and WorkOrderCount are supposed to be the total and count for the entire report, then they should never be reset to 0. In that case, remove the {@grp footer1} formula.

James

ASKER

Yes, the TotalHours and WorkOrder Count are for each individual doctor. That's why I thought I was suppose to put the formulas in the GF.

> Yes, the TotalHours and WorkOrder Count are for each individual doctor.

> That's why I thought I was suppose to put the formulas in the GF.

Ah, good point. Let me rephrase my question.

Are TotalHours and WorkOrderCount meant to be used to calculate the average for each individual doctor, or the average for the whole report?

If you want to calculate the average for each doctor, you would need to reset those variables to 0 for each doctor, so that you would get a separate average for each doctor. For example, in the doctor group header, you would reset the variables to 0, and then in the doctor group footer, you would calculate the average for that doctor.

If you want to calculate the average for the entire report, then you should _not_ reset those variables, so that at the end of the report, they will have the combined totals from all of the doctors on the report. You would then use those combined totals to calculate the average for all of the doctors on the report.

James

> That's why I thought I was suppose to put the formulas in the GF.

Ah, good point. Let me rephrase my question.

Are TotalHours and WorkOrderCount meant to be used to calculate the average for each individual doctor, or the average for the whole report?

If you want to calculate the average for each doctor, you would need to reset those variables to 0 for each doctor, so that you would get a separate average for each doctor. For example, in the doctor group header, you would reset the variables to 0, and then in the doctor group footer, you would calculate the average for that doctor.

If you want to calculate the average for the entire report, then you should _not_ reset those variables, so that at the end of the report, they will have the combined totals from all of the doctors on the report. You would then use those combined totals to calculate the average for all of the doctors on the report.

James

ASKER

Thanks James TotalHours and WorkOrderCount were meant to used to calculate for each individual doctor. When I put it in the GF it returned numbers like 0.03 or 0.

mlmcc I looked at the report and it seems that the dotors' averages are coming up as 0.03, 0.05, and the total average is coming up as the same thing 0.03. dr. nanavati's average is 22 when I did the calculation manually

mlmcc I looked at the report and it seems that the dotors' averages are coming up as 0.03, 0.05, and the total average is coming up as the same thing 0.03. dr. nanavati's average is 22 when I did the calculation manually

We would need data to track that kind of error.

mlmcc

mlmcc

<chuckle> I just spent a while looking at the report that mlmcc posted and I don't know how many times I looked at the formulas, checking one thing or another, and then it finally hit me - Both of the average formulas are reversed. Both @avgrage and @RptAverage divide the count by the hours, instead of the other way around. Obviously, that will probably give you very low results in most cases.

I had noticed at one point that the average formulas were checking to see if the count was 0 and not doing the division if it was (to avoid a divide by 0 error), but they weren't dividing by the count. I just thought the tests were backwards and was going to mention that they should be changed. It wasn't until a while later that I realized that the tests were right and the division was wrong. :-)

So, reverse the division in those two formulas and see what you get.

James

I had noticed at one point that the average formulas were checking to see if the count was 0 and not doing the division if it was (to avoid a divide by 0 error), but they weren't dividing by the count. I just thought the tests were backwards and was going to mention that they should be changed. It wasn't until a while later that I realized that the tests were right and the division was wrong. :-)

So, reverse the division in those two formulas and see what you get.

James

ASKER

I reversed the division and............IT WORKS!!!!!!! Thank you two soooo much!!! :o) I couldn't have done this without you!

You're welcome. Glad I could help.

James

James

Otherwise, simply select the formula and then click the Sigma button.