Calculating overtime in pivot table

[Akkio]

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,

[harikm007 · Accepted solution]

Hi @Akkio ,

Try if multi-pass aggregation gives you right result. So the formula becomes:

SUM([Week], 
  CASE when (SUM([Total Punched Hours]+[Total Vacation Hours]+[Total Holiday Hours]),) < 
   SUM([Total Hours day])) then 0
  else (SUM([Total Punched Hours]+[Total Vacation Hours]+[Total Holiday Hours])) - 
   SUM([Total Hours day]))
  END
)

https://documentation.sisense.com/docs/build-formulas

-Hari

[Akkio]

Hi Hari,

Thank you so much! I'm getting the correct results now.