Akkio
Data Storage
Joined 4 years ago
User Profile
User Widgets
Contributions
Calculating overtime in pivot table
Hi all, I'm not sure why I'm not getting the right value of overtime per employee when i remove the column week. We calculate the overtime on a weekly basis. Basically an employee has an overtime when the sum of his punched hours (the hours he worked for the week) + the sum of his vacations days ( 8 hours per day if he has vacations for a specific week) + the sum of holiday hours ( 8 hours per day if there's a Holiday for a specific week) is less than 48 hours. Here is an example for the employee ID 30028 : He worked for week 35 , 39.63 hours + he had one vacation day (8hours) , total = 47.63 < 48 , so overtime = 0 For week 36 he worked 48.47 hours , so he has 0.47 hour in overtime. But HR team wants to see the overall total of overtime for a given day period (we're using a calendar filter) without having to do the calculation manually. So when i remove the column week , i get 0.1 in overtime , which is not the correct value. It's like it's doing (47.63 + 48.47) - 96 , which is 0.1 overtime. Instead of 47.63 < 48 so overtime = 0 + 48.47 > 48 so overtime = 0.47 , total = 0.47. Here is my formula : CASE when (SUM([Total Punched Hours]+[Total Vacation Hours]+[Total Holiday Hours]),[Week]) < (SUM([Total Hours day]),[Week]) then 0 else (SUM([Total Punched Hours]+[Total Vacation Hours]+[Total Holiday Hours]),[Week]) - (SUM([Total Hours day]),[Week]) END Thanks for the help,Solved2.5KViews0likes2Comments