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

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?
Best Answers
-
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
2 -
Hey Deona,
Break this calculation down into steps:
- Creation of ticket date
- First response date
- Days between creation and first response date
- Weekend days between creation and first response date
- Weekday days between creation and first response date.
- The column is given
- given
- datediff(`creation_date`,`first_response_date`)
- You can use the SQL queries provided or the magic ETL date operations column to calculate the working days difference between 2 dates.
- Column #3 - column #4
2 -
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.
2
Answers
-
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
2 -
Hey Deona,
Break this calculation down into steps:
- Creation of ticket date
- First response date
- Days between creation and first response date
- Weekend days between creation and first response date
- Weekday days between creation and first response date.
- The column is given
- given
- datediff(`creation_date`,`first_response_date`)
- You can use the SQL queries provided or the magic ETL date operations column to calculate the working days difference between 2 dates.
- Column #3 - column #4
2 -
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.
2
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 667 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive