Help with calculating average

If the formula depends on a summary, it can't be summed.

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

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.

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?

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

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

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

The average is not necessarily the sum of the averages.

I changed the timediff2 and average formulas

mlmcc
TurnoverOrthopedicsrev1.rpt

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

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

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

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

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

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

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

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.

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

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

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 ) .

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

I tried that and it still gives me the records with zeros
GushchinTurnover-1-.rpt

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

THANK YOU!!!!

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:

.

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

 James

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

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

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

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.

Check the changes I made

mlmcc
TurnoverOrthopedicsRev1.rpt

> 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

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

We would need data to track that kind of error.

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 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