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
- 1 APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 252 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 63 Kowaza
- 296 仲間に相談
- 649 ひらめき共有