Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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.

1 Solution

Accepted Solutions
swuehl
Not applicable

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

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

18 Replies
swuehl
Not applicable

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

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?

Not applicable

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

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

swuehl
Not applicable

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

It should be feasible and not too complicated  using advanced aggregation aggr(), maybe like

= sum( aggr(if(sum(hours) > 40, hours-40), 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

Not applicable

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

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.

Not applicable

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

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))

Not applicable

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

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

Not applicable

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

If I recall correctly you can't use aggr with set analysis, so would need to

do something like this:

sum( aggr(if(sum(Hours) > 40 and = year(Today()),

sum(Hours)-40),

, Employee))

Not applicable

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

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())

Not applicable

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

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))