Sum of Time/Day Issue

[Dayna]

Hello All,

I have an application that analyses the time that is logged within a SQL database, originally, all was fine as there wasn't many records entered. However, now there's a lot of data I seem to have an issue! I had a field with a random number that spat out the time that was logged, so I used this expression:

 Time(TIMETAKEN / (24 * 60 * 60 * 1000)) 

But now there's so much data, when I sum a value I get the wrong result, as it's converting it to hours:minute and ignoring the possibility there may be a couple of days worth.

I'm not sure how to provide an example data, but what I need to show is the amount of time and show it in the format of days:hours:minutes based on my data.

Please see a link from a question I had regarding this: http://community.qlik.com/forums/p/39449/155424.aspx#155424

Many thanks for your help!

KInd Regards,
Dayna

[rbecher]

Hi Dayna,

presuming you having a duration in seconds (maybe TIMETAKEN / 1000 ?) you can use this

=floor(sum(DURATION_SECS)/3600)&':'&right('0'&floor(mod(sum(DURATION_SECS),3600)/60),2)&':'&right('0'&mod(sum(DURATION_SECS),60),2)

- Ralf

[Dayna · Accepted solution]

Hello Ralf,

Many thanks for your reply! I ended up using Interval() and specifying day within the formatting, but thank you all the same!

Kind Regards,
Dayna