Hi everyone!
I have the following columns in the data source:
- Pay Descriptions that have categories: REG, for regular hours, and OT1, for Overtime.
- Date
- Round Time In
- Round Time Out
- Total Hours
However, regular time and overtime records have the same listed start and end times, and there are dates with just overtime hours. For example,
Pay Description: REG, Date: 3/18/2023, Round Time In: 9:45:00, Round Time Out: 13:31:00, Total Hours: 3.77
Pay Description: REG, Date: 3/18/2023, Round Time In: 14:01:00, Round Time Out: 19:30:00, Total Hours: 4.12
Pay Description: OT1, Date: 3/18/2023, Round Time In: 14:01:00, Round Time Out: 19:30:00, Total Hours: 1.37
Pay Description: OT1, Date: 3/19/2023, Round Time In: 11:30:00, Round Time Out: 16:01:00, Total Hours: 4.52
Pay Description: OT1, Date: 3/19/2023, Round Time In: 16:34:00, Round Time Out: 20:30:00, Total Hours: 4.52
This causes a double count of Overtime hours when I use this formula to create a Tableau heat map and filter worked Hours by hour REG and OT1.
To count time worked, I'm using this calculation: IF DATEPART('hour',[Round Time In])=DATEPART('hour',[Hour])
THEN (60-DATEPART('minute',[Round Time In]))/60
ELSEIF DATEPART('hour',[Round Time Out])=DATEPART('hour',[Hour])
THEN DATEPART('minute',[Round Time Out])/60
ELSE 1
END
The problem could be solved by unselecting OT1 from the filter since I just need actual hours worked. However, it would miss the days of the employee who just did Overtime.
Note:
I created a relationship in Tableau between the data source from Alteryx and an hour's bucket table in Excel to be able to get worked Time by hour in the heat map
DATEPART('hour',[DateTime in]) <= DATEPART('hour',[Hour])
DATEPART('hour',[DateTime Out]) >=DATEPART('hour',[Hour])
Any idea how I can avoid the double count in overtime hours?
