Crystal 2013 maxium value of a sum 1

[dmontalvo]

I'm a very new user to Crystal. I'm trying to create a formula (s) to produce the average of a labor hours excluding the maximum value.

Currently this is what I have setup:

Report is titled "Average Hrs by Dept"

Purpose - labor hours are to be listed for each employee per day grouped by department. An average of labor hours by dept is one result and then we also want an average without maximum value as well.

Group 1: Employee.Department
Group 2: Employee.Employee
Group 3: Job_Operation_Time.Work_Date

Labor hours are currently listed for group 3 totaled by day, totaled by group 2 then finally group 1.

The average without max formula would be an average of total employee labor hours / dept.

I prepared a formula to obtain the average hours per department that looks like this:

sum({Job_Operation_Time.Act_Run_Labor_Hrs},{Employee.Department})/{#Count}

However when it comes to the max value I'm stuck.

Any help would be appreciated.

 

[dmontalvo]

CoSpringsGuy

Yes need the single highest total hr for employee subtracted from the sum.

Yes I did that calculation incorrect by dividing by 6 - it should be divided by 5 since the 1 max ee count must be taken out.

thank you.

 

[CoSpringsGuy]

ok my last try and I have to catch a plane out of the country for a few weeks

delete the last set of formulas I gave you
create this one and add to group footer 2 and suppress

global numbervar max2;
Global numbervar cnt2;
whileprintingrecords;
if Sum ({Sheet1_.hours}, {Sheet1_.Emp}) > max2 then max2 := Sum ({Sheet1_.hours}, {Sheet1_.Emp});
cnt2 := cnt2 +1;
max2;

create this formula and add to report footer dont suppress

global numbervar max2;
Global numbervar cnt2;
whileprintingrecords;
(Sum ({Sheet1_.hours})-max2)
/
(cnt2-1)

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[dmontalvo]

THANK YOU - SUCCESS AGAIN
SAFE TRAVELS

SUGGESTIONS ON HOW TO FIND GOOD TRAINING ON CRYSTAL IS APPRECIATED.

 

[CoSpringsGuy]

I am self taught and learned a lot from this forum and a couple of others. Also check out

http://www.kenhamady.com.

He has a couple of good books on formulas and advanced formulas and techniques, plus can do one on one training as well.


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[dmontalvo]

THANKS AGAIN FOR YOUR TIME!!

 

[SkipVought]

@demontalvo,

Please use "Great Post? Star it" link in the helpful post(s) to award a star for helpful post. This way others see which post you found beneficial, and it says: 'Thank you' to whoever helped you.

 

[dmontalvo]

@skipvought

Will do, sorry I'm new to forums.

 

[SkipVought]

@demontalvo,

Not a problem. Appreciate your participation.

 

[dmontalvo]

question.
I see the "great post" on each post.

Is it necessary to tag each post in the entire communication or perhaps just the one with the answer?

 

[SkipVought]

Just the post(s) that were particularly helpful to you.

If you browse these forums, you'll see threads with multiple stars, and anyone, not just the thread originator, can hit the link if they were helped.