Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
PLE
Contributor II
Contributor II

Sum Hours from Timestamp

Hello, 

I'm trying to do a sum of hours that are stored as timestamps in our database.

Example, we have various jobcards for a mechanic to perform, and each jobcard has a time allocated to perform the action.

This time allocated is stored as

01/01/1900 03:00:00 - it can take maximum 3 hours

01/01/1900 01:30:00 - it can take maximum 1 hours and 30 minutes

 I would like to make a sum of the time allocated to some jobcards. In this example, the result would be 4 hours and 30 minutes. Then this number should be multiplied by the manhour rate to budget the cost of these jobcards.

When I use =TimeStamp([Quote Task Mhr],'hh:mm:ss'), qlik gives me hh:mm:ss format that I can't sum correctly,

When I use =num(time([Quote Task Mhr])), qlik gives me decimal values that I can't relate to hours or minutes.

Can somebody help me with this please.

Thanks,

Paul

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

You can try this

=Time(Sum(frac([Quote Task Mhr])))

View solution in original post

6 Replies
sunny_talwar

You can try this

=Time(Sum(frac([Quote Task Mhr])))

brunobertels
Master
Master

Hi 

try this 

time(
Timestamp('01/01/1900 03:00:00','hh:mm:ss')
+

timestamp('01/01/1900 01:30:00','hh:mm:ss')
,'hh:mm:ss')

 

will give you : 

04:30:00 

PLE
Contributor II
Contributor II
Author

Hi Sunny, thanks for the idea. It seems to work.

Below picture of 1) raw timestamp data 2)data as time/sum/frac 3)data multiplied by 100 eur / hour

Qlik Sense - Untitled - Table - September 29, 2020.png

Thanks again.

PLE
Contributor II
Contributor II
Author

Hi Bruno, thanks for the idea, but I'm afraid that's not going to work.

My database contains around 2000 lines of timestamp data, so a more general formula is what I'm looking for.

sunny_talwar

Awesome

PLE
Contributor II
Contributor II
Author

You have a trick to show results as decimals, example 2,5 for 2 hours and 30 minutes?