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
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive