How to convert HH:MM:SS string data type for time analysis purpose?

Q&A
, , ,
1

image
image1419×442 36.5 KB

I want to do analysis on this type of data. I tried to convert this with calculated field.
But when I want to do analysis, it is stored at string and just giving me count and count unique options.
I want to do time analysis based on this data. Can you please guide? Thank You!

2

Hello Anagdeve,

First of all Welcome to the Quick Sight community.

Have you tried parseDate Function ? if not try it out.

See reference here - parseDate - Amazon QuickSight

Hope this helps.

Cheers,

Deep

3

Hi Deep,
Thank you for your time. I did it but it is not working.
Like I did parseDate({Online Time}, [‘HH:MM:SS’]) but still not working showing this error ” At least one of the arguments in this function does not have correct type.”

4

HEllo Anagdeve,

What is the actual format/value of {Online Time} that you’re trying to parse?

The error “At least one of the arguments in this function does not have correct type” typically occurs when:

  • The input data format doesn’t match the expected format

  • The format string isn’t properly formatted

  • The input value might be null or in an unexpected format

Thanks for checking.

Cheers,

Deep

5

Thank you Deep for your time again. I have 27 columns which show time in hh:mm:ss format without dates.

I want to show all the time frames in front of employee names.

In the CSV file, the format is custom time [h]:mm:ss format. After many edits in the calculated field, it is still showing as count 1 instead of time in front of employee names.

The actual format/value of {Online Time} is [h]:mm:ss in csv file and on quick-sight shows string that I am trying to convert.

6

Hi @anagdeve,

Hope everything is well! Just checking in since this thread hasn’t received a response in a while. Were you able to find a solution to your question regarding formatting in the meantime? If not, please feel free to comment with any questions or any persisting issues. If this thread does not receive a response within the next 3 business days, I’ll close/archive out of this topic.

Thank you!

7

I am still looking for this solution so that I can use it as TIME data.
Can we have function in quick sight apart from calculated field to cover time and date data separately?

8

Hi @anagdeve,

Since it seems Quick Sight is currently not able to natively handle time durations that are over 24 hours, the only way most likely would be to utilize calculated fields.

For this case, you can try converting the time duration to seconds so that you can do data analysis from there, and for visuals you can play around with calculated fields to display the trends as time duration (i.e. hh:mm:ss). If that does not work, you may have to restructure your data in Excel (or somewhere else outside of Quick Sight). I can also definitely bring up your most recent question as a feature request so that it may be implemented in the future!

1 Like
9

Thank you on this. Hope this the new function will help many users.