Hello everyone,
I wanted to share a solution I found for converting seconds to HH:MM:SS in Amazon QuickSight. When using the formula provided by the QuickSight community in a calculated field, the result is in string format, which isn’t suitable for aggregation. After searching for a solution and not finding any source, I managed to figure it out. Here’s a step-by-step guide:
Step 1: Create a calculated field (e.g., “SumTimeInterval”) and use the formula:
sum(seconds)
Step 2: Create another calculated field (e.g., “time”) to convert seconds to HH:MM:SS:
concat(
ifelse(SumTimeInterval / 3600 < 10,concat(‘0’,tostring(decimalToInt(floor(SumTimeInterval / 3600) ))),tostring(decimalToInt(floor(SumTimeInterval / 3600))))
,‘:’,
ifelse((SumTimeInterval % 3600)/60 < 10,concat(‘0’, tostring(decimalToInt(floor((SumTimeInterval % 3600)/60)))),tostring(decimalToInt(floor((SumTimeInterval % 3600)/60))))
,‘:’,
ifelse(SumTimeInterval % 60 < 10,concat(‘0’,tostring(decimalToInt(floor(SumTimeInterval % 60)))),tostring(decimalToInt(floor(SumTimeInterval % 60))))
)
Step 3: Create a visual using the table type, add any column in the group by, and place the “time” calculated field in the value. In the field wells, you will see “time (Custom)” in the value, indicating that it works correctly.
we are using the calculated field of sum(seconds) rather than directly creating calculated field for converting seconds to HH:MM:SS
Now, you can perform aggregations on the “time” calculated field. I hope this solution proves helpful for you. If you have any questions or suggestions, feel free to ask.
