Calculation

[Mayra2291]

Hi everyone!

I have the following columns in the data source:  

1. Pay Descriptions that have categories: REG, for regular hours, and OT1, for Overtime. 
2.  Date 
3. Round Time In 
4. Round Time Out 
5. 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?

[caltang]

The concept here is to get what is duplicated and make the data spit out only OT1 if it exists with REG.

In your case, the hierachy of importance is that REG triumphs over OT1 - so if both rows share similar dates, start and end times, then the row with REG will be taken. If OT1 exists without any REG equivalent based on the dates, start and end times, then it exists without being removed. Likewise with single REG.

Let me know if this helps @Mayra2291 

[Hammad_Rashid]

To avoid double-counting overtime hours in your Tableau heat map, you can modify your calculation to exclude overlapping hours between regular and overtime shifts. You can use the following approach to calculate the actual hours worked by an employee in each hour:

1. Calculate Regular Hours: Calculate regular hours using the formula you provided for regular hours.

   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

2. Calculate Overtime Hours: Calculate overtime hours using a similar formula.

   IF DATEPART('hour', [Round Time In]) = DATEPART('hour', [Hour]) THEN 0 ELSEIF DATEPART('hour', [Round Time Out]) = DATEPART('hour', [Hour]) THEN DATEPART('minute', [Round Time Out]) / 60 ELSE 1 END

3. Combine Regular and Overtime Hours: Create a combined calculated field that adds regular and overtime hours.

   [Regular Hours] + [Overtime Hours]

   This will give you the total worked hours for each hour in your heat map, with overtime hours only counting when there is no overlap with regular hours.

4. Adjust Filters: When creating your heat map, you can filter out records where only overtime hours are worked if needed, while still including employees who work both regular and overtime hours.

These adjustments should help you avoid double-counting overtime hours in your Tableau heat map while still capturing the total hours worked by employees, including those who work overtime on certain days.

[Mayra2291]

Hi @caltang,

Thank you so much for your response. For some reason, I'm getting 'REG" instead of 'OT1' in the formula, and I'm getting this result. 

[Mayra2291]

Thank you so much for your response. Unfortunately, this formulas keep overlapping  regular and overtime hours.

[caltang]

Have you looked at the final result? Your screenshot looks like what you described. Not sure what the issue is - it's working as intended no?

Because if they exist at the same date, same time in, and same time out, you only wan the REG time and not the OT1 times since they overlap - right?

[Pang_Hee_Choy]

maybe just adjust the time_in and time_out based on total hours.

then continue to build the hours heatmap with the adjusted time