topic Sum Time in Hours (HH:MM) in QlikView

Sum Time in Hours (HH:MM)

Fri, 29 Apr 2011 20:01:20 GMT

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!!!

Sum Time in Hours (HH:MM)

Anonymous — Fri, 29 Apr 2011 20:12:35 GMT

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')

Sum Time in Hours (HH:MM)

spividori — Fri, 29 Apr 2011 20:18:03 GMT

Hi Thiago.

See the example. Hope this help!.

Regards.

Re:Sum Time in Hours (HH:MM)

Fri, 29 Apr 2011 20:43:44 GMT

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!!!

Re:Sum Time in Hours (HH:MM)

Fri, 29 Apr 2011 21:18:27 GMT

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!

Re:Sum Time in Hours (HH:MM)

johnw — Fri, 29 Apr 2011 21:28:06 GMT

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.

Re:Sum Time in Hours (HH:MM)

Fri, 29 Apr 2011 22:00:07 GMT

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!!!

Re:Sum Time in Hours (HH:MM)

johnw — Fri, 29 Apr 2011 22:18:36 GMT

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

Re:Sum Time in Hours (HH:MM)

Mon, 02 May 2011 13:41:02 GMT

Hello John!

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

Re:Sum Time in Hours (HH:MM)

Mon, 02 May 2011 13:46:53 GMT

test

//Upload working fine for me.

Re:Sum Time in Hours (HH:MM)

Mon, 02 May 2011 15:09:41 GMT

There's the file!

Started my week being a NOOB! =$

Standing by your lightshed John!

Thanx everyone!

Re: Sum Time in Hours (HH:MM)

marjan_it — Tue, 12 Aug 2014 10:22:51 GMT

how to get sum of 2 time field that have more than 24 hours

I want to get sum 2 time in expression in qlikview like this: 20:25:50 and 22:45:15

And in result show this:43:11:05

Re: Sum Time in Hours (HH:MM)

rubenmarin — Tue, 12 Aug 2014 10:49:19 GMT

As John said,

try Interval(TotalTempoDisponibilidade, 'hh:mm') as TotalTempoDisponibilidade

Edit: sorry didn't see the date of the post. Marjan, you can use Interval(Sum(TimeValueField), 'hh:mm:ss')

Regards

Re: Sum Time in Hours (HH:MM)

marjan_it — Wed, 13 Aug 2014 04:31:51 GMT

but my TimeValueField is in 2 different field: field A:20:25:50 and field B: 22:45:15

Re: Sum Time in Hours (HH:MM)

vinieme12 — Fri, 10 Mar 2017 09:26:55 GMT

Like below

load Sum(Interval(Time,'hh:mm')) as TotalTime inline [

Time

00:20

00:36

01:15

06:37

];

Re: Re:Sum Time in Hours (HH:MM)

PASTORGAA — Tue, 21 Apr 2020 00:03:38 GMT

thanks for your reply

best regards