Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee

[#1]

I am trying to show a chart that shows Hours per week, and Over time hours per week.

I am running into a problem where I need to tell the expression to take a summary for the week based on the employee, and if that number is over 40 to subtract 40 to get the over time hours.

How can I tell it to do this?

Example:

Employee       Hours per week

1                    30

2                    50

3                    20

4                    60

5                    40

The correct overtime for this example is 30. Standard hours is 170.

Calculating Standard as 40hr/employee would show 200 standard hours and 0 over time.

[#2]

This seems to not give the expected results (See Image)

Expressions:

Current Year Standard:

sum(aggr(if(sum(Hours)>40 and [Year Payroll]=Year(Today()), Sum(Hours)-(sum(Hours)-40),if([Year Payroll]=Year(Today()),Sum(Hours))), [Week Payroll], Employee))

Current Year OT:

sum( aggr(if(sum(Hours) > 40 and [Year Payroll]=Year(Today()), sum(Hours)-40), [Week Payroll], Employee))

Last Year Standard:

sum(aggr(if(sum(Hours)>40 and [Year Payroll]=Year(Today()-1), Sum(Hours)-(sum(Hours)-40),if([Year Payroll]=Year(Today()-1),Sum(Hours))), [Week Payroll], Employee))

Last Year OT:

sum( aggr(if(sum(Hours) > 40 and [Year Payroll]=Year(Today()-1), sum(Hours)-40), [Week Payroll], Employee))

[#3]

Ok, lets try this:

Current Year Total Hours =

sum

( aggr(if([Year Payroll] = year(Today()), sum(Hours)), [Week Payroll], Employee))

Current Year OT =

sum

( aggr(if(sum(Hours) > 40 and [Year Payroll] = year(Today()), sum(Hours)-40), [Week Payroll], Employee))

Current Year Regular = 

sum

( aggr(if([Year Payroll] = year(Today()), sum(Hours)), [Week Payroll], Employee))

-

sum( aggr(if(sum(Hours) > 40 and [Year Payroll] = year(Today()), sum(Hours)-40), [Week Payroll], Employee))

[swuehl]

Marc,

I'm not sure if this is just a typo in your post, but year(today()-1) is giving the same as year(today()) for most of this years dates, because you subtract 1 from today(), not from year(today()). Try changing your above expressions to ... year(today())-1...

Or do you wanted to indicate that even this years numbers for OT and standard are not correct?

Besides this, I believe that you indeed can use set expression with advanced aggregation, so you might just want to add your above set expression to your aggr() function, maybe also to the embedded sums.

Hope this helps,

Stefan

[#5]

Yes that was a typo. Meant to type year(today())-1. I also tried adding the set to the expression in the different locations and it seems not to work correctly.

[#6]

I tried you updated formulas, but that did not seem to change much. When I have no Year selected I expect to see the same results as if one was. IE Week 3 has 2,640 standard hours for 2011 when I do not select 2011. When 2011 is selected the standard hours for week 3 change to 3,230.

[swuehl]

Marc,

I've just seen your attachement above. Could you post an update with your current status or more data across the year(s) here?

Stefan

[#8]

Here is a new sample.

[swuehl · Accepted solution]

Thanks,

your sample looks a bit complex to me 😉 and in the meanwhile I built a small self containing sample.

Please find attached.

Is this what you were looking for?

I used expressions similar to those I posted above and included the set expressions for CY and LY.

Note also that I used weekstart as dimension for the aggregations, since week seems not to be unique over years.

Hope this helps,

Stefan

[#10]

That works great, thank you so much for your help.