Getting the Avg Days, Avg Hours, Avg Minutes
Hello. May I ask for your help in getting the average days or average hours or average minutes between 2 columns of date + timestamp data?
The thing is, the average could be 'days', 'hours' or 'minutes', depending on the values of the 2 data columns, so we want to show the output similar to a timestamp format to be precise, since we could not determine or put unit of measure whether the output is in 'days' or 'hours' or 'minutes'.
See below example of output. If the output is 17 hours, we want to see that in
'DAYS HOURS:MINUTES' or somehow similar to '0 17:00'. But if you have a different approach for this one, please let us know. Thank you
Best Answers
-
I think you want something like:
CONCAT(
DATEDIFF(QB-Closed-Date, QB-Created-Date), ' ', //days
HOUR(TIMEDIFF( QB-Closed-Date, QB-Created-Date)), ':', //hours
MINUTE(TIMEDIFF( QB-Closed-Date, QB-Created-Date)) //minutes
)
(Adapted from @JaySethi in this discussion)
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
The issue that you will run into with @DavidChurchman solution is that the field will then be a text value and you will not be able to get an average.
I would use something like this to get the number of seconds between the two values.
You can then find the average of that numeric value. Then the issue becomes one of converting the average seconds between the two values to the format you want.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2
Answers
-
Hi @Zel
What do your input datasets look like? Also, are you wanting the ETL to calculate the Averages for you, or are you going to find the average at the card level?
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
hi @ST_-Superman-_!
ETL or card level will do, as long as we could come up with the solution on how to display the average and its format - as mentioned in the original post.
The 2 columns look like this: QB-Created Date and QB-Closed Date.
0 -
I think you want something like:
CONCAT(
DATEDIFF(QB-Closed-Date, QB-Created-Date), ' ', //days
HOUR(TIMEDIFF( QB-Closed-Date, QB-Created-Date)), ':', //hours
MINUTE(TIMEDIFF( QB-Closed-Date, QB-Created-Date)) //minutes
)
(Adapted from @JaySethi in this discussion)
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
all of your Closed Dates are actually occurring prior to the open date? From your screenshot, all of the elapsed times will be negative.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
The issue that you will run into with @DavidChurchman solution is that the field will then be a text value and you will not be able to get an average.
I would use something like this to get the number of seconds between the two values.
You can then find the average of that numeric value. Then the issue becomes one of converting the average seconds between the two values to the format you want.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2
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
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 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