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
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 434 Beast Mode
- 22 Variables
- 511 Automate
- 114 Apps
- 389 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive