Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Sum of Time Over 24 Hours

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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.

View solution in original post

8 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Creator II
Creator II

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

Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
Creator II
Creator II

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

Highlighted
MVP & Luminary
MVP & Luminary

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.

View solution in original post

Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

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

Highlighted
Contributor III
Contributor III

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