Sum of Time Over 24 Hours

[dinicholls]

Hi All,

This is confusing me no end!

I have a simple pivot adding up up the hh:mm:ss made on calls. Its a date / time field, and I'm just pulling out the time.

The problem occurs when the sum of the time goes over 24 hours, and then I basically lose 24 hours!

Instead of showing 25 hours, 36 minutes and 2 seconds as 25:36:02, it shows 01:36:02!

How do I get it to either show 25:36:02, OR  1 01:36:02?

I've tried 'interval' but that didn't seem to work. Any ideas?!

Have attached a screen dump to show you what I mean.

Thank you!

Di

[jagan]

Hi,

Try like this

Data:

LOAD * ,

Time#(Time, 'hh:mm:ss') AS FormattedTime

INLINE [

Time

15:36:02

10:36:02

];

In frontend

=Interval(Sum(FormattedTime), 'D hh:mm:ss')

OR

=Interval(Sum(FormattedTime), 'hh:mm:ss')

Hope this helps you.

Regards,

Jagan.

[dinicholls]

Hi,

I'm sorry, but I don't understand why I would do an inline table? What is the purpose?

Can you explain please? Sorry, just want to understand why.

I won't have to put every time in it, will I? These change on a daily basis!

Thanks

Di

[jagan]

Hi,

There is no need of Inline table, I just added for sample data purpose, you can change the script accordingly in your data.

Data:

LOAD * ,

Time#(Time, 'hh:mm:ss') AS FormattedTime

FROM TableName;

In frontend

=Interval(Sum(FormattedTime), 'D hh:mm:ss')

Regards,

Jagan.

[dinicholls]

Hi,

Thanks for explaining, I like to understand the why's and what for's and use it in the future, rather that just ask for the answer!

Have tried what you suggested, and no change.

Any other ideas?

I can't post the data because of company policy, unfortunately.

Thanks

Di

[jagan · Accepted solution]

Hi,

Inline table is used to load some tables to the Qlikview datamodel, suppose you have some category table

LOAD

*

INLINE [

Product, Category1

P1, C1

P2, C1

P3, C1

P4, C2];

If the time field is in the time format then you can directly use

=Interval(Sum(TimeFieldName), 'D hh:mm:ss')

Regards,

Jagan.

[dinicholls]

Hi again!

I've still had no luck!

I've checked the formatting, and added 'D hh:mm:ss' instead of just 'hh:mm:ss', and that just put a 30 before all the times!

This is soo frustrating!

Di

[dinicholls]

Hi,

I think I may have cracked it with your help!!!

Had a play and changed the settings to 'Interval', rather than 'hh:mm:ss', and it appears to have worked!

Thank you!

Di

[Marcellino_Groothof]

Hi,

Try this in the expression,

Interval(Sum(timeactive),'hh:mm')

then goto tabblad number and change it to interval, that works well

Regards,

Marcellino