Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum Time in Hours (HH:MM)

Greetings!!!

I have a little issue, and have exhausted my ideas and found no solution...

I have a field that contains time amounts like in the format 'HH:MM', like that

TIME
----------------------------
00:20
00:36
01:15
06:37

And I want to create a chart to show the total amount of time per Day/Month/Year etc... but the SUM() function does not work properly, and I don't know how to dismenber the data to sum it the right way.

Any thoughts?

Thank you all!!!

15 Replies
Anonymous
Not applicable
Author

This test worked for me, I got 50 min as result:
=time(num(time#('00:20', 'hh:mm')) + num(time#('00:30', 'hh:mm')) , 'hh:mm')

So, it may look like this for you:
=time(sum(num(time#(TIME,'hh:mm'))),'hh:mm')

spividori
Specialist
Specialist

Hi Thiago.

See the example. Hope this help!.

Regards.

Not applicable
Author

Sandro!!! Thx for the example!!!

But I'm getting a weird result in the charts!

I noticed that some of the data are on this format:

59:59:00

Can this be the issue!!??

Thank you again!!!

Not applicable
Author

Michael,

I tried to use your formulas, and they work! But for the reason I told Sandro, the results still creepy!

There are a lot HH:MM:SS info in the field... how can I take away the SS part without messing with the data? I tried using SUBSTRING() and RIGHT(), but with no sucess...

This Y axis scale is driving me crazy!

johnw
Champion III
Champion III

Don't use time(). Use interval(). Time is a clock time, so is limited to 24 hours. Interval is a duration, so can be as long as necessary. Sandro's example uses "interval", though in this case as just a format on the number tab.

It appears that your chart is turning your times or intervals into dates. Hopefully setting it as an interval in either the data or on the number tab will fix that. I have no problem, for instance, if I convert Sandro's example to a bar chart.

As for taking away the SS data, just override the format. Like this, for intance:

interval(sum(MyInterval),'hh:mm')

Depending on what you're doing, that could also just be an override format on the number tab.

Not applicable
Author

Hello John!

I got the idea, and have used your function on my script...

Weirdly, nothing changed! =(

If I upload a QVW with the data of this field, can you, or anyone, look into it? This is a monster bump on my project, and is causing me a lot of trouble...

Again, thank you very very much!!!

johnw
Champion III
Champion III

Yeah, it's always easier to debug when we have something to look at. Upload away!

Not applicable
Author

Hello John!

The Forum does not allow me to upload the QVD/QVW with the data... where am I going wrong? =S

Not applicable
Author

test

//Upload working fine for me.