I want to get actual after business hours from start time to end time. Business hours is between 8am to 17:00pm. I am struggling with events such as highlighted. Start date is within Business hours and End time is out of Business hours. my desired results is to see only actual After Business hours. In the example I expect hours between start of out of business hours 5:00pm to end time 7:30 pm = 2 hours 30 minutes. Other scenarios if start is 6am and end is 10am, it would be 6am to 8am(start of business hours) = 2 hours actual out of business hours
You can create two new 'anchor' time values for the start/end of your business hours:
Business Hour Start:
MAKEDATETIME(DATE([TIME START_DATE]),MAKETIME(8,0,0))
Business Hour End:
MAKEDATETIME(DATE([TIME START_DATE]),MAKETIME(17,0,0))
Then can calculate the difference when before/after:
After/Before Business Hours (Measure):
IF [TIME START_DATE]<[Business Hour Start] THEN DATEDIFF('minute',[TIME START_DATE],[Business Hour Start])/60
ELSEIF [TIME END_DATE]>[Business Hour End] THEN DATEDIFF('minute',[Business Hour End],[TIME END_DATE])/60
END
The same calc can be then modified to identify whether the row is Before/After as a new Dimension:
IF [TIME START_DATE]<[Business Hour Start] THEN 'Before Business Hours'
ELSEIF [TIME END_DATE]>[Business Hour End] THEN 'After Business Hours'
ELSE 'Business Hours'
END
Bring it altogether:
Preferably though, would probably be better done in Tableau Prep if there are any other considerations or asks here.
Best, Don Wise -
Please don’t forget to upvote and/or Select as Best by clicking the hyperlink below in the response that answered your question.
