Need to convert the aggregated column to HH:MM format/HH:MM:SS format

Question & Answer
,
1

I have a table like below with dates for 2 years, student name and school entry time.

table_content
table_content1618×774 102 KB

My goal is to find the average school entry time of past 7 days for each student.

i have used this calculation field and a filter to calculate the average converted_time (aggregated)
calculation field - converted_time - “parseInt(substring(EntryTime,1,2))*60+(parseInt(substring(EntryTime,4,2)))”

converted
converted1531×750 63.3 KB

Now i want to convert the avg_converted_time back to original time format i.e in HH:MM/HH:MM:SS . Is it possible if so how can i convert it ?

2

Hi @ajith

You might need to convert Entry time to seconds and take average round with out any decimals . Then you could use below calculated field expression to convert number to HH:MIM:SS format .

concat(

ifelse({Average Talk Time} / 3600 < 10,concat(‘0’,tostring(floor({Average Talk Time} / 3600))),tostring(floor({Average Talk Time} / 3600)))

,‘:’,

ifelse(({Average Talk Time} % 3600)/60 < 10,concat(‘0’,tostring(floor(({Average Talk Time} % 3600)/60))),tostring(floor(({Average Talk Time} % 3600)/60)))

,‘:’,

ifelse({Average Talk Time} % 60 < 10,concat(‘0’,tostring(floor({Average Talk Time} % 60))),tostring(floor({Average Talk Time} % 60)))

)

Please find the example in QuickSight DemoCentral .

https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-Duration-Time-Format-HH-MM-SS-

Thanks
VInod

3

Hi @apjvinod ,

I have aggregated the convert_seconds column by average

I have created a calculation field as you told

Now when i add converted_averagetalktime to visual i am getting only the count of all values in "converted_averagetalktime " column i am not able to get any time

snap_avgtalktime_1
snap_avgtalktime_11545×690 70.4 KB

4

Hi @ajith ,

Please add all the fields in GROUPBY Section.
Right now you have added Student Name in ‘GROUPBY’ and your calc in ‘VALUES’, Now what QS doing is it is taking count of your calc based on Student Name.

BR

5

If i move “convert_seconds” to GROUP BY i wont be able to compute the average of “convert_seconds” the option is not available

6

Hello @ajith , @Vaibhav_Patidar , and @apjvinod !

@ajith were you able to find a solution or workaround for this problem or do you still need help? If you were able to find a solution could you share it to help the community?

7

Hello @ajith , @Vaibhav_Patidar , and @apjvinod !

@ajith would you be able to share your concat calculated field that you created? I’m curious how you built the calculated field like @apjvinod suggested but ended with the values in your converted_averagetalktime field.

It has been some time since there has been activity on this thread but we would still like to help you find a solution. If we do not hear from you in 3 business days this topic will be archived.

8

Hi @duncan I was not able get any solution for this .

9

Hey @ajith !

Could you share an anonymized view of the calculated field you built based on @apjvinod 's suggestion? I’m curious if there is something we can troubleshoot there to get the values you are looking for.

10

Hi @duncan , Sorry You can close/archive this issue. I have deactivated my quicksights account i wont be able to share it.

1 Like