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
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 474 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 214 Visualize
- 259 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 189 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive