CODEWITHSUNDEEP
tableau-apitableau-desktop
user13687410
user13687410

Reputation:

Calculate business hours between two dates in Tableau

I have two fields Ticket created date and ticket replied date. The difference between these two dates will give me the response time for each ticket.

I am looking at creating a calculated field where the time for each ticket doesn't calculate if the ticket has come in during non-business hours/days.

So the first response time should be calculated only during business hours/day.

I have made a calculated field to understand which are business days and business hours but not able to figure out how to get the difference between tickets created and replied to based on these.

Upvotes: 0

Views: 314

Answers (1)

Pongphon Tan
Pongphon Tan

Reputation: 46

I assume you needed to find a date/time difference in business days/hours (excluding weekend) between two datetime. In that case, do you have a chance to check on this Tableau knowledge base article?
Calculating Business Hours Between Two Timestamps

Or if you need to calculate only date/time difference when neither Ticket created date nor ticket replied date are fall in non-business day. Please try below code snippet:

IF DATEPART('weekday',[Ticket created date], 'Sunday') = 1 OR DATEPART('weekday',[Ticket created date], 'Sunday') = 7 THEN
    NULL
ELSEIF DATEPART('weekday',[ticket replied date], 'Sunday') = 1 OR DATEPART('weekday',[ticket replied date], 'Sunday') = 7 THEN
    NULL
ELSE
    DATEDIFF('day', [Ticket created date], [ticket replied date])
    /* DATEDIFF('hour', [Ticket created date], [ticket replied date]) */
END

Note that, in DATEPART, I set start_of_week = 'Sunday' to avoid issue when date/time setting of the data source connection isn't Sunday

Ref: How do I tell if a date falls on a weekend?

Upvotes: 0

Related Questions