I have an aggregate with session start time and last active action time attributes. I'm building an analytics dashboard and one of the requirements is average active session time. I have an aggregate with a calculated column that calculates the difference between those two attributes in seconds, and then the output is just the average of those seconds. I want to convert that to HH:mm:ss using FormatDateTime(), but the docs say HH only goes from 0 to 24. How do I make sure the HH:mm:ss output accounts for a scenario where the average is 25 hours or greater?
My current working expression:
If(Trunc(GetSessionLogs.List.Current.SessionTimeSecondsAvg / 3600) < 10, "0" + Trunc(GetSessionLogs.List.Current.SessionTimeSecondsAvg / 3600), Trunc(GetSessionLogs.List.Current.SessionTimeSecondsAvg / 3600))
+ ":" +
If(Trunc(Mod(GetSessionLogs.List.Current.SessionTimeSecondsAvg, 3600) / 60) < 10, "0" + Trunc(Mod(GetSessionLogs.List.Current.SessionTimeSecondsAvg, 3600) / 60), Trunc(Mod(GetSessionLogs.List.Current.SessionTimeSecondsAvg, 3600) / 60))
If(Round(Mod(GetSessionLogs.List.Current.SessionTimeSecondsAvg, 60)) < 10, "0" + Round(Mod(GetSessionLogs.List.Current.SessionTimeSecondsAvg, 60)), Round(Mod(GetSessionLogs.List.Current.SessionTimeSecondsAvg, 60)))
Any better way to do this?
Explanation:
Hours: uses an If() to add a leading zero if less than 10, otherwise gets the total hours and uses Trunc() to discard the remainder which gets handled in the minutes chunk.
Minutes: uses an If() to add a leading zero if less than 10, uses Mod() to get the remaining minutes after getting the hours, then Trunc() discards the remainder of that which gets handled in the seconds chunk.
Seconds: uses an If() to add a leading zero if less than 10, uses Mod() to get the remaining seconds after getting the minutes, then rounds up to the nearest second.
Try to avoid the repetitive divisions, instead use local variables for hours, minutes and seconds. If those locals are integer, there's also no need for Trunc. So you get something like:
Hours = SecAvg / 3600
Minutes = Mod(SecAvg, 3600) / 60
Seconds = Mod(SecAvg, 60)
Instead of the if condition for adding the leading zero you could try FormatText
ex: FormatText(Hours, 2, 19, True, "0")
I've implemented a client action that does that calculation