Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can try this
=Time(Sum(frac([Quote Task Mhr])))
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
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
Thanks again.
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.
Awesome
You have a trick to show results as decimals, example 2,5 for 2 hours and 30 minutes?