Thought I would write about this Analytics Quick Tip as I have found zero information related to this online and I would imagine it would be a pretty common use case.
The Problem With Decimal Hours
Often you have ‘duration’ type measures stored as ‘number of seconds.’ Think of the duration of a service ticket from one stage to the next, or the duration of a sales call. Many times you want to aggregate all of these together for a particular user or particular day. Once you’ve aggregated them together, you can easily convert these into decimal hours by dividing by 60 to arrive at # of minutes and 60 again to arrive at # of hours (divide by 3600 once to shortcut this step). The trouble is then you have a confusing metric of decimal hours. 3.45 hours does not equal 3 hours and 45 minutes. It is closer to 3 hours and 30 minutes! 4.87 does not equal 4 hours and 87 minutes! No matter how many times you try and educate your users on decimal hours it is confusing for them to discuss with their teams and properly rank and compare them to each other.
A SAQL Solution For Converting Decimal Hours to HH:MM
I think it’s helpful to break each step down into its component part before we get into the code of how we are going to accomplish this.
We want to take 4.87 and convert to 4:52.
- First we want to take out the # preceding the decimal point as it is already a valid # of hours. (4)
- Then we want to add a colon to separate hours from minutes. (4:)
- Then we want to take the remaining number post decimal point and multiply by 60 (this number will be formatted to only 2 digits and placed after the colon). (4:52 – we arrive at this by taking the entire 4.87 and subtracting the truncated number of 4 which leaves .87 and then we multiply that by 60)
Simple corresponding example below without formatting and where ‘decimalhour’ is already in decimal hours. You would just replace ‘decimalhours’ with your duration measure and divide by 3600 if converting from seconds.
1. trunc(sum('decimalhours')) 2. + ":" + 3. sum('decimalhours') - trunc(sum('decimalhours')) * 60
Here is an example of the full saql w/ formatting.
number_to_string(trunc(sum('decimalhours')),"#") + ":" + number_to_string(((sum('decimalhours')) - (trunc(sum('decimalhours')))) * 60,"0#") as 'HH:MM'
Now we have a very easy to read table in hours and minutes!
The only issue I’ve found is when someone is right on the hour, say 4:00 and it only displays a single zero. I’ve solved for this with a case statement as in all of my use cases we are 5 hours and below so I only need 5 case statements. This is very easy to do. I had first tried replace but it would then replace all scenarios where there is a zero after the colon. I’m sure there is a more elegant solution but I have not had the chance to spend very much time on this piece and the case statement solved my particular situation.
Did this solution help you? Do you have another method to solve the same problem? Let me know in the comments. I would love to hear from you!