How to get total sum of data in a column format dd:hh:mm
Comments
-
If I understand correctly, you're looking for a way to sum the time values within a column that are currently formatted to show dd:hh:mm. I broke it down by converting each section to minutes and then extracting the amount of days, hours and minutes in the final "minute sum".
This beast mode seems to work in my testing. Just replace the 'Time' column throughout the code with whatever column you are wanting to sum. Hope this helps!
CONCAT(
FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)
,':',
FLOOR((SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))-(FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)*1440) )/60 )
,':',
SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))
-
(FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)*1440)
-
(FLOOR((SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))-(FLOOR(SUM(SUBSTRING(`Time`,0,INSTR(`Time`,':'))*24*60
+
SUBSTRING(`Time`,INSTR(`Time`,':')+1,INSTR(`Time`,':'))*60
+
RIGHT(`Time`,2))/1440)*1440) )/60 )*60)
)1 -
Thank you ValiantSpur this is close to what I need.
Is it possible that in my chart, the label and the legend will be formatted as dd:hh:mm ?
Please see the attached file.
Appreciate your help.
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 308 Workbench
- 7 Cloud Amplifier
- 10 Federated
- 3.8K Transform
- 660 Datasets
- 117 SQL DataFlows
- 2.2K Magic ETL
- 818 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 84 App Studio
- 46 Variables
- 781 Automate
- 191 Apps
- 483 APIs & Domo Developer
- 84 Workflows
- 23 Code Engine
- 43 AI and Machine Learning
- 22 AI Chat
- 3 AI Projects and Models
- 18 Jupyter Workspaces
- 408 Distribute
- 116 Domo Everywhere
- 281 Scheduled Reports
- 11 Software Integrations
- 146 Manage
- 142 Governance & Security
- 8 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 115 Community Announcements
- 4.8K Archive