Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
arsallee3
New Contributor III

Calculate Regular and Overtime Hours

Good day Community,

Have a problem that have not been able to solve. Given the below data I would like to calculate the regular time hours & overtime hours for each team member by Region,Week #. and I have just been stumped.

Appreciate any thoughts as probably making this tougher than should be.

Thanks

Data feed     

EECodeRegionWeek #DayEarnHours
0379Eagle117.68
0379Eagle117.23
1082Midcon115.03
1082Midcon115.58
0379Eagle117.05
1082Midcon115.13
0370Eagle117.33
1082Midcon115
0370Eagle117.27
0370Eagle18.87
1082Midcon20.93
1082Midcon215.13
1082Midcon214.85
0379Eagle214.95
0379Eagle214.82

Desired result in pivot table

      

RegionWeekEECodeTotal HoursRegular TimeOvertime
Eagle    125.2109.7715.43
  1  95.438015.43
    037051.964011.96
    037943.47403.47
  2  29.7729.770.00
    037929.7729.770.00
Midcon    91.6570.9120.74
  1  60.744020.74
    108260.744020.74
  2  30.9130.910.00
    108230.9130.910.00
Grand Total  216.85180.6836.17
Tags (1)
1 Solution

Accepted Solutions

Re: Calculate Regular and Overtime Hours

This?

Capture.PNG

3 Replies

Re: Calculate Regular and Overtime Hours

This?

Capture.PNG

arsallee3
New Contributor III

Re: Calculate Regular and Overtime Hours

That is perfect Sunny.

Just for my edification, if I wanted to add or reduce the pivot summary just add or reduce the Aggr name summary at the end of the statement. Would that be correct?

Re: Calculate Regular and Overtime Hours

Well it would depend... isn't that straight forward.... you need to aggregate on the lowest level to make sure your check for < or > 40 hours is still valid... if you do it Region and Week #, removing EECode, you might not get the results you desired.... but I guess play around with it to see what works for you....