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.

 

[SkipVought]

Hi,

Very unusual!

So to get an average you need the sum of a sub-sample and the sum of the total sample.

Your requirement is to exclude the MAX value. Does that mean ONLY the MAX for the sub-sample but include ALL values in the total sample, or does it mean to remove every MAX for each sub-sample and then do the averaging?

 

[dmontalvo]

Hi

Thanks for the reply.

Excluding Max to produce average by department only not for report totals.

 

[SkipVought]

Read my question more carefully. I am not referring to anything but the calculation for AVERAGE.

 

[dmontalvo]

ok.

Guess I'm not clear on your terms "sub-sample" and total sample.

I need an average of total sample excluding MAX value of the total sample.

 

[dmontalvo]

Not sure if this would help but in excel this formula that would produce my desired result is:


=(sum(A1:A3)-MAX(A1:A3))/MAX(1,count(A1:A3)-(countif(A1:A3,MAX(A1:A3)))))

where A1:A3 are formulas that total labor hours per the employee/department.

 

[CoSpringsGuy]

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

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[dmontalvo]

cospringsguy

thank you for your reply.

this formula is producing a result however its not the same result that excel is producing.

What can I show you so you can see the crystal report design and what the result is.

 

[CoSpringsGuy]

I think your excel formula is removing the max from EACH employee as well?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[dmontalvo]

cospringsguy

hmm, I think excel is producing a correct result. The excel version does not total labor hours per day per employee. It simply list a value as a total hours per employee.

Please advise if you agree with this calculation:

Accounting Dept:
Employee 1 - total labor hours 44.25
Employee 2 - total labor hours 40.25
Employee 3 - total labor hours 41.50

Accounting Dept total hours 126.00 showing 3 employees

the excel formula produces a result of 40.88 when using the excel formula I posted.

When using the crystal formula you provided it produces a result of 39.00

 

[CoSpringsGuy]

opk i see why .. give me a few minutes and I will provide a solution

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

OK my solution is probably way unorthodox but that is how I do most things in life! This is all assuming I understand your desired results of course. If I dont then I suppose this will do you no good. Its also quite a pain in the butt but here goes. I created a spreadsheet to simulate your data with Column 1 - Dept. Column 2 - Employee and Column 3 Hours
My report is grouped on
Group #1 Dept
Group #2 Employee
(You had a third group but that shouldn't effect this)
In header 1 place this formula replacing the database field names with your own. Right click the formula select format field - common tab and select suppress

global numbervar Avglessmax := 0;
global numbervar max := 0;
Global numbervar cnt := 0;
whileprintingrecords;

In header 2 place this formula replacing the database field names with your own. Right click the formula select format field - common tab and select suppress

global numbervar Avglessmax;
global numbervar single := Sum ({Sheet1_.hours}, {Sheet1_.Emp}); //account for dept with only 1 employee if that exists
global numbervar max;
Global numbervar cnt;
whileprintingrecords;
cnt := cnt +1;
if max = 0 then max := Sum ({Sheet1_.hours}, {Sheet1_.Emp});
if  Sum ({Sheet1_.hours}, {Sheet1_.Emp}) < max then
(Avglessmax := Avglessmax + Sum ({Sheet1_.hours}, {Sheet1_.Emp}));
 if  Sum ({Sheet1_.hours}, {Sheet1_.Emp}) >  max then
(
Avglessmax := Avglessmax + max;
max := Sum ({Sheet1_.hours}, {Sheet1_.Emp})
);
Avglessmax

In Group 1 Footer place this formula replacing the database field names with your own and do not suppress

global numbervar Avglessmax;
global numbervar single;
global numbervar cnt;
whileprintingrecords;
if cnt = 1 then single
else if cnt > 1 then 
    Avglessmax/(cnt-1)
    else Avglessmax;

Let me know how that works

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

My results using the method above

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[dmontalvo]

CoSpringsguy:

SUCCESS! Thank you.

Now what if I wanted an average excluding MAX for ALL employees ALL departments?

So as to show as a company total in report footer.

 

[CoSpringsGuy]

That can be done the same way if you want to wrestle with it. I am going to be tied up for a bit here but will try and provide the solution for that as well by end of day?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[dmontalvo]

Absolutely I will try it on my own.

thanks for your help. Enjoy your day.

 

[CoSpringsGuy]

If you named your formulas as above then create a new one called
Accum2 (If you named them differently replace {@result} with the name you used for your formula)
place this formula in Group Footer 2 and suppress

global numbervar totalhours;
global numbervar counthours;
whileprintingrecords;
totalhours := totalhours + {@result};
if {@result} > 0 then counthours := counthours +1;
totalhours

Place this in Report footer and do not suppress

global numbervar totalhours;
global numbervar counthours;
whileprintingrecords;
totalhours
/
counthours

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

correction.. that first formula should be placed in Group Footer 1

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[dmontalvo]

CoSpringguy

THANK YOU.

using your formula I get result but it's a smidge off from my manual calc.

Just to be clear if we used this last formula on your example above I should arrive at 35.17.

That's total hours (255.50 - 44.50)/6 employees.

The current result is off .10 from my manual calculation.

Does that make sense to you?

 

[CoSpringsGuy]

ok we can fix that but ....
for the report totals you want the one single highest hour from the entire report to be subtracted from the sum? I was doing something different.
if that is what you want, wouldnt it be (255.50 - 44.50)/5 because you took the one employee out?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection