
Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Stefan Wühl Oct 28, 2011 12:06 PM (in response to Marc Livingston)What about using two expressions like
=if([Hours per week] >40, [Hours per week]40)
for overtime hours and
=if([Hours per week] <=40, [Hours per week], 40)
for standard hours?

Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 28, 2011 12:17 PM (in response to Stefan Wühl )Let me explain more on how my chart is set up. (Overtime is given per employee who works over 40 hours in one week)
Current Dimension: week([Date Payroll])
Expression should be sum(hours) for that week per employee. If one employee works 60 hours that week it should record 20 hours of OT and 40 Standard.
If another employee works 30 hours it should show 0 OT hours and 30 Standard.
Instead I see Total hours=90
Standard (# employees *40)=80
Overtime hours=10
I pretty much need to take a sum of hours per week grouped by the sum employee hours but I do not know how to sum per employee when that is not a dimension

Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Stefan Wühl Oct 28, 2011 12:27 PM (in response to Marc Livingston)It should be feasible and not too complicated using advanced aggregation aggr(), maybe like
= sum( aggr(if(sum(hours) > 40, hours40), weekPayroll, employee))
for overtime hours.
AFAIR, aggr doesn't take calculated dimensions, so you might need to add a week(DatePayroll) as weekPayroll to your data model.
If you could post a small sample, I believe someone might figure out the correct expression quite fast.
Regards,
Stefan

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 28, 2011 1:29 PM (in response to Stefan Wühl )I tried your expression, and I only get "No Data to Display" on the chart.
I will try and come up with a sample that I can post.

d.qvw 161.5 K

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Leonard Short Oct 28, 2011 2:26 PM (in response to Marc Livingston)One slight change to the expression will make it calculate accurately in your sample.
=sum ( aggr(if(sum(Hours) > 40, sum(Hours)40), [Week Payroll], Employee))

Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 28, 2011 2:51 PM (in response to Leonard Short )That does let it work. Now as a final question that relates to that, how would I use a set analysis for this?
Need to add
{<[Year Payroll]={$(=Year(Today()))}>} so it only pulls info for this year


Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 28, 2011 3:14 PM (in response to Leonard Short )Um I am guessing you did not mean to post a link there? I am also guessing that the first link should be [Year Payroll]=Year(Today())

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Leonard Short Oct 28, 2011 3:17 PM (in response to Marc Livingston)Interesting, wonder what outlook did this time... let me try this again...
sum( aggr(if(sum(Hours) > 40 and [Year Payroll] = year(Today()), sum(Hours)40), [Week Payroll], Employee))

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 28, 2011 3:50 PM (in response to Leonard Short )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))

Untitled.png 40.2 K

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Leonard Short Oct 28, 2011 4:20 PM (in response to Marc Livingston)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))

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 31, 2011 7:40 AM (in response to Leonard Short )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.


Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Stefan Wühl Oct 29, 2011 8:20 AM (in response to Marc Livingston)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

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 31, 2011 7:38 AM (in response to Stefan Wühl )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.

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Stefan Wühl Oct 31, 2011 7:48 AM (in response to Marc Livingston)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

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 31, 2011 8:18 AM (in response to Stefan Wühl )Here is a new sample.

sample.qvw 9.9 MB

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Stefan Wühl Oct 31, 2011 8:45 AM (in response to Marc Livingston)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

payroll.qvw 279.0 K

Re: Trying to show overtime vs standard hours per week. Need to calculate based on hours per employee
Marc Livingston Oct 31, 2011 9:03 AM (in response to Stefan Wühl )That works great, thank you so much for your help.














