Average Total Hours for Month

[FireGeek21]

I am having a problem calculating the average of total hours for the month. This needs to be done by person and by pay date.

Sample data:
Person1 PayDate1 HrsWrk1 18.50
Person1 PayDate2 HrsWrk1 17.50
Person1 PayDate3 HrsWrk1 14.25
Avg = 16.75
Tot = 50.25

Person2 PayDate1 HrsWrk1 80.00
Person2 PayDate1 HrsWrk2 -16.00
Person2 PayDate2 HrsWrk1 80.00
Person2 PayDate3 HrsWrk1 80.00
Person2 PayDate3 HrsWrk2 -16.00

My efforts are showing correctly for Person1 but for Person2 my efforts are showing an average of 41.60. It is calculating 208/5 instead of 208/3 (3 pay periods). Looking for 208/3 = 69.33 for average for Person2.

Thanks for your help!

FireGeek21



FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[IanWaterman]

YOu can not use the Standard Avg function in Crystal, you will need to replace with a formula

@Avg

If distinctCount(PayDateField, PersonGrpField) <> 0 then // this prevents divide by zero errors
Sum(Valuefield, PersonGrpField)/distinctCount(PayDateField, PersonGrpField)

Ian

 

[FireGeek21]

Awesome!

Can this be done with a SQL Expression?

I have a total hours SQL Expression that is working well and would be interested in the Average in SQL if possible.

(SELECT SUM("PRTIME"."HOURS")
FROM "LAWSON"."PRTIME"
WHERE {fn YEAR("PRTIME"."PER_END_DATE")} = 2012
and {fn MONTH("PRTIME"."PER_END_DATE")} = 11
and ("PRTIME"."PAY_SUM_GRP" = 'CLI' or
"PRTIME"."PAY_SUM_GRP" = 'CLO' or
"PRTIME"."PAY_SUM_GRP" = 'DSO' or
"PRTIME"."PAY_SUM_GRP" = 'EDU' or
"PRTIME"."PAY_SUM_GRP" = 'NP' or
"PRTIME"."PAY_SUM_GRP" = 'OVT' or
"PRTIME"."PAY_SUM_GRP" = 'PG1' or
"PRTIME"."PAY_SUM_GRP" = 'PG2' or
"PRTIME"."PAY_SUM_GRP" = 'PG3' or
"PRTIME"."PAY_SUM_GRP" = 'RG1' or
"PRTIME"."PAY_SUM_GRP" = 'RG2' or
"PRTIME"."PAY_SUM_GRP" = 'RG3' or
"PRTIME"."PAY_SUM_GRP" = 'RGX' or
"PRTIME"."PAY_SUM_GRP" = 'WAR' or
"PRTIME"."PAY_SUM_GRP" = 'WKH')
and "LAWSON"."PRTIME"."COMPANY" = "EMPLOYEE"."COMPANY"
and "LAWSON"."PRTIME"."EMPLOYEE" = "EMPLOYEE"."EMPLOYEE")



FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

Ian,

I tried modifying the formula as follows:

IF Month(PayDateField) = 10 THEN
(
If distinctCount(PayDateField, PersonGrpField) <> 0 then // this prevents divide by zero errors
Sum(Valuefield, PersonGrpField)/distinctCount(PayDateField, PersonGrpField)
)

... and then created one for November and December...

I get zeros for each.

Guess I am looking to display the average in month colums across like:

January February March April.....




FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[IanWaterman]

I forgot the else in my original formula

@Avg

If distinctCount(PayDateField, PersonGrpField) <> 0 then // this prevents divide by zero errors
Sum(Valuefield, PersonGrpField)/distinctCount(PayDateField, PersonGrpField)
else 0

So your formula should be

@AvgOct
IF Month(PayDateField) = 10 THEN
(
If distinctCount(PayDateField, PersonGrpField) <> 0 then // this prevents divide by zero errors
Sum(Valuefield, PersonGrpField)/distinctCount(PayDateField, PersonGrpField)
else 0
)
0

You will need to place this in Person Group Footer, you will need formula for each month

@AvgNov
IF Month(PayDateField) = 11 THEN
(
If distinctCount(PayDateField, PersonGrpField) <> 0 then // this prevents divide by zero errors
Sum(Valuefield, PersonGrpField)/distinctCount(PayDateField, PersonGrpField)
else 0
)
0

Re SQL expression question
This does not contain a grouping so you cannot perform avearge and counts etc.

Ian

 

[FireGeek21]

Frustrating!!!

When I open up the date range in Report Selection to include more months other than just say November, the average flips to zero!

I am using your formula:

@AvgNov
IF Month(PayDateField) = 11 THEN
(
If distinctCount(PayDateField, PersonGrpField) <> 0 then // this prevents divide by zero errors
Sum(Valuefield, PersonGrpField)/distinctCount(PayDateField, PersonGrpField)
else 0
)
0

Need to show this average for each month and although this appears to work, it's only working when the date range being pulled is one month. Need to pull 12 months total. ARUGH!


FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

Well, I pressed on! I did find a solution I thought I would share with everyone...

Summary of What is Needed:
Looking for average hours worked per month. The months/averages need to be listed across in separate columns:

January February March April... etc.

I started with a basic employee table for my report. I have grouping on an employee id field.

Next, I made a Total and an Average SQL Expression Field for each month. The month function in each expression is changed for each month.

12-TOTAL (December):
(SELECT SUM("PRTIME"."HOURS")
FROM "LAWSON"."PRTIME"
WHERE {fn YEAR("PRTIME"."PER_END_DATE")} = 2012
and {fn MONTH("PRTIME"."PER_END_DATE")} = 12
and "LAWSON"."PRTIME"."COMPANY" = "EMPLOYEE"."COMPANY"
and "LAWSON"."PRTIME"."EMPLOYEE" = "EMPLOYEE"."EMPLOYEE")

12-AVERAGE (December):
(SELECT SUM("PRTIME"."HOURS") / COUNT(DISTINCT "PRTIME"."PER_END_DATE")
FROM "LAWSON"."PRTIME"
WHERE {fn YEAR ("PRTIME"."PER_END_DATE")} = 2012
and {fn MONTH("PRTIME"."PER_END_DATE")} = 12
and "LAWSON"."PRTIME"."COMPANY" = "EMPLOYEE"."COMPANY"
and "LAWSON"."PRTIME"."EMPLOYEE" = "EMPLOYEE"."EMPLOYEE")

Notice the last 2 lines of each SQL Expression Field are actually linking this SQL Expression Field back to the EMPLOYEE table that the report is built on. Both the COMPANY and EMPLOYEE fields are key fields needed for linking these two tables.

I hope this helps someone. Thank you Ian for your efforts!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)