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

deona720
deona720 Member
in Beast Mode

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?

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited July 15 Answer ✓

    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

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor
    Answer ✓

    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
    Sean_Tully Contributor
    edited July 15 Answer ✓

    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.

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited July 15 Answer ✓

    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

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor
    Answer ✓

    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
    Sean_Tully Contributor
    edited July 15 Answer ✓

    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.

Categories