Last tested: Sep 11, 2019
Some data is best displayed in smaller time units, such as seconds, but with aggregation of large datasets, the seconds value gets so large it is relatively meaningless. In that regards, it would be easier to display the data in another format.
We can use the value format parameter as listed in this document, but to properly use the Excel style formatting for time we need to first divide our aggregation by the number of seconds within a day.
On either the raw measure or on a duplicate for formatting purposes
- Divide your sql parameter by the number of seconds in a day (60sec/min *60min/hr *24hrs/day) = 86400.0
- Adding the decimal creates a floating point that accommodates for the decimals.
- Add the value_format parameter to your field and use the appropriate level of detail.
- The most conservative option is [hh]:[mm]:ss
- If you only need to the minutes level: [mm]:ss
Viola! Easily readable aggregated time