Calculation
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
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 ENDCalculate 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 ENDCombine 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.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much for your response. Unfortunately, this formulas keep overlapping regular and overtime hours.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Alteryx ACE
https://www.linkedin.com/in/calvintangkw/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
