Converting String to TIME and Average

Question & Answer
, , ,
1

Hello Guys ! im facing an issue where im not able to convert the “Time_Assigned and Time Closed fields Which are string to general HH:mm:ss Format
I would like to find a difference between both so as in for the first field a new Calculated field has to be populated with the Data " 00:06:28”

Also i have to find the Average of this as well ; Since AVG formula doesnt accept dates / time Kinda stuck here .
1

Any help is appreciated over this community or Discord

2

Hello @s4doto, welcome to the QuickSight Community! There is definitely a route to resolve this, but it is a little complex so I’ll try to give some details.

These steps should get you close to the result you are looking for:

  • Split the string into 3 fields- Hours, minutes, and seconds using this substring function.
  • For each new field you can parse it to an integer with parseInt() and then I would recommend converting each to seconds
  • create another calculated field that will then create the new format. I am going to link this demo analysis that was built by AWS. This is an amazing tool for this specific functionality and has pre-built calculated fields that you can dig into.

For more information, this is also a link for another QuickSight community question that was asked/answered related to this topic.

Let me know if this helps!

3

Hello @s4doto, did my response help you resolve your issue in QuickSight? If so, feel free to mark it as a solution. If not, please follow-up with some more details about the issue you are facing and I can try to guide you toward a solution. Thank you!

4

i was able to get a work around with the formula
concat(toString(floor({Floater - Time}/3600)),‘:’,toString(floor(({Floater - Time}%3600)/60)),‘:’,toString({Floater - Time}%60))

Down side is this converts the time into string