Average time to response but how do I not include weekends?

deona720

I am making a dashboard summarizing some trends with Jira tickets and trying to create a metric card that shows the current average time to first response, I am using this formula (which I think I got off this forum):

CONCAT(
case when ROUND((((86400DATEDIFF([CHART] Date of First Response,Created))+(time_to_sec(TIMEDIFF([CHART] Date of First Response,Created))))/60)/60 - 0.5, 0) <= 9 then '0' else '' end,
ROUND((((86400DATEDIFF([CHART] Date of First Response,Created))+(time_to_sec(TIMEDIFF([CHART] Date of First Response,Created))))/60)/60 - 0.5, 0))

But we are finding out the average is quite high because of the fact that weekends will be an extra 48 hours to first response. How can I make this only weekdays?

ColemenWilson

You can use the DAYOFWEEK() function to identify and remove weekends - days 1 and 7 (saturday and sunday)

CASE WHEN DAYOFWEEK(`YourDateField`) NOT IN(1,7) THEN Your beastmode END

nmizzell

Hey Deona,

Break this calculation down into steps:

1. Creation of ticket date
2. First response date
3. Days between creation and first response date
4. Weekend days between creation and first response date
5. Weekday days between creation and first response date.

1. The column is given
2. given
3. datediff(`creation_date`,`first_response_date`)
4. https://domo-support.domo.com/s/article/360043428153?language=en_US You can use the SQL queries provided or the magic ETL date operations column to calculate the working days difference between 2 dates.
5. Column #3 - column #4

Sean_Tully

I think there's a way to do this by figuring out the day of week of the created field, the day of week of date of response, and the datediff of the two. You should be able to calculate how many weeks turned over from those three points of data, and therefore how many 2-day weekends which you can subtract within your beastmode, but I don't have enough time to play with it at the moment.

ColemenWilson

You can use the DAYOFWEEK() function to identify and remove weekends - days 1 and 7 (saturday and sunday)

CASE WHEN DAYOFWEEK(`YourDateField`) NOT IN(1,7) THEN Your beastmode END

nmizzell

Hey Deona,

Break this calculation down into steps:

1. Creation of ticket date
2. First response date
3. Days between creation and first response date
4. Weekend days between creation and first response date
5. Weekday days between creation and first response date.

1. The column is given
2. given
3. datediff(`creation_date`,`first_response_date`)
4. https://domo-support.domo.com/s/article/360043428153?language=en_US You can use the SQL queries provided or the magic ETL date operations column to calculate the working days difference between 2 dates.
5. Column #3 - column #4

Sean_Tully

I think there's a way to do this by figuring out the day of week of the created field, the day of week of date of response, and the datediff of the two. You should be able to calculate how many weeks turned over from those three points of data, and therefore how many 2-day weekends which you can subtract within your beastmode, but I don't have enough time to play with it at the moment.