Forum Discussion

Akkio's avatar
Akkio
Data Storage
09-16-2022
Solved

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 ...
Human Resources Analytics
  • harikm007's avatar
    harikm007
    09-19-2022

    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

harikm007's avatar
harikm007
Data Warehouse
09-19-2022

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's avatar
    Akkio
    Data Storage
    09-19-2022

    Hi Hari,

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