Solved

Help with calculating average

Posted on 2012-12-26
36
221 Views
Last Modified: 2013-01-15
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
0
Comment
Question by:vgriffin0423
  • 17
  • 9
  • 7
  • +1
36 Comments
 
LVL 22

Expert Comment

by:Ido Millet
ID: 38721977
If the formula depends on a summary, it can't be summed.

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

Expert Comment

by:Ido Millet
ID: 38722036
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.
0
 

Author Comment

by:vgriffin0423
ID: 38722061
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?
0
 
LVL 22

Expert Comment

by:Ido Millet
ID: 38722171
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
0
 

Author Comment

by:vgriffin0423
ID: 38723480
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
0
 

Author Comment

by:vgriffin0423
ID: 38724864
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38724963
The average is not necessarily the sum of the averages.

I changed the timediff2 and average formulas

mlmcc
TurnoverOrthopedicsrev1.rpt
0
 

Author Comment

by:vgriffin0423
ID: 38726647
Thanks mlmcc, i put the average forumla in the group footer but the calculations are not correct. What am I doing wrong?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38726980
I would need to see the data to figure that out.

mlmcc
0
 

Author Comment

by:vgriffin0423
ID: 38727032
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38727928
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
0
 

Author Comment

by:vgriffin0423
ID: 38728003
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 38728882
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
0
 

Author Comment

by:vgriffin0423
ID: 38732409
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38732791
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
0
 

Author Comment

by:vgriffin0423
ID: 38736267
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38737460
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
0
 

Author Comment

by:vgriffin0423
ID: 38737888
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 38738924
WHat is 0?

WHen you count and do the total

If {YourField} <> 0 then
(
    Add and count
);

mlmcc
0
 

Author Comment

by:vgriffin0423
ID: 38739809
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 ) .
0
 
LVL 34

Expert Comment

by:James0628
ID: 38740312
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
0
 

Author Comment

by:vgriffin0423
ID: 38740714
I tried that and it still gives me the records with zeros
GushchinTurnover-1-.rpt
0
 

Author Comment

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

THANK YOU!!!!
0
 

Author Comment

by:vgriffin0423
ID: 38740853
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:

.
0
 
LVL 34

Expert Comment

by:James0628
ID: 38743076
Did you get it to work then?  Just making sure.

 James
0
 

Author Comment

by:vgriffin0423
ID: 38759516
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38761433
WHy are you setting the counts and totals to 0 in the group footer?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 38762152
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
0
 

Author Comment

by:vgriffin0423
ID: 38764360
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.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38765515
Check the changes I made

mlmcc
TurnoverOrthopedicsRev1.rpt
0
 
LVL 34

Expert Comment

by:James0628
ID: 38766337
> 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
0
 

Author Comment

by:vgriffin0423
ID: 38767764
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38768128
We would need data to track that kind of error.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 38769786
<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
0
 

Author Comment

by:vgriffin0423
ID: 38774286
I reversed the division and............IT WORKS!!!!!!!  Thank you two soooo much!!! :o)  I couldn't have done this without you!
0
 
LVL 34

Expert Comment

by:James0628
ID: 38777320
You're welcome.  Glad I could help.

 James
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now