Problem with summing time periods over 10 hours!

[snehamahaveer]

    Hi ,

I am calculating the total time spent for a particular activity by adding all the activity times over a period of a month.

The values are correct if the total is below 10 hours, how do I make sure that the addition of activity time period is correct for bigger values.?

I use, = sum(time(endtime-starttime, 'hh:mm')) to calculate. Also is it possible to divide the time by another time?

eg. = sum(time(endtime-starttime, 'hh:mm')) / Working_hours

Thanks in advance

[snehamahaveer]

Hi ,

If I use 'm'/60 - the value reduces to 5:05:07.

If I use 'm'/1440 - the value is even lower

If its just m, it comes to 17:07:52

All three values are wrong. I am missing some vital information / the values are in a different format that makes the final result to be wrong?

[snehamahaveer]

Please find the start and end dates on this sheet, the contact hours are on the list as well. I want a sum of the contact hours to be displayed.

[rubenmarin]

Hi Sneha, I meant a sample of the document, anyway this can help, check attachment, with this xls and the expression "Interval(Sum(endtime-starttime), 'hh:mm')" it gives 102:33. Wich is the same than Excel returns.

So maybe the problem is the way you're loading the dates and times in script.

[snehamahaveer]

Ruben,

Thanks, it is due to privacy issues I am unable to upload the test document.

My data is from an SQL upload and does not include the timestamp function. Would that be the case for the data to be displayed incorrectly?

The issue is only with values over 10 hours, below that it works absolutely fine.

[sasiparupudi1]

May be you need to format the timestamp..

It might be coming in AM/PM format  12/12/2015 08:30:30 PM 12/12/2015 06:30:02 AM

in that case,you could use timestam# with your matching format code

interval(timestam#(startdate,'YYYY/MM/DD hh:mm:ss TT')-timestam#(startdate,'YYYY/MM/DD hh:mm:ss TT'),'hh:mm')

HTH

Sasi

[snehamahaveer]

Hi Sasidhar,

Its in 24 hour format.

[Colin-Albert]

This post has useful links to several articles about getting date and time data in the correct format.

Re: date formats

[rubenmarin]

Yes, that points to a formatting problem, seems that values with two characters on the hour part gives you the problem.

The timestamp function is from QV, did in the LOAD section, so you can use it, but maybe you should use also timestamp#() to tell Qv the format of the timestamp readed, something like:

LOAD Timestamp(Timestamp#(SQLField, 'DD/MM/YYYY h:mm:ss')) as

...

FROM SQL ...

or

LOAD Timestamp(Timestamp#(SQLField, 'DD/MM/YYYY hh:mm:ss')) as

....

or LOAD Timestamp(Date(Left(SQLField, 10)) + Time(Right(SQLField, 8)))

or formatting the field on SQL

Many options... that's why I wanted a sample, to aim the shoot.

[krishna20]

Hi Sneha,

Is this are you looking for???

[sasiparupudi1]

load

ACTIVITY_SUB_TYPE,ENDDATE,[Start date and time],[CONTACT HOURS],

interval(timestamp#(ENDDATE,'YYYY/MM/DD hh:mm')-timestamp#([Start date and time],'YYYY/MM/DD hh:mm'),'hh:mm') as [CONTACT HOURS QV]

inline

[

ACTIVITY_SUB_TYPE,ENDDATE,Start date and time,CONTACT HOURS

Cold Call,2015/06/19 15:00,2015/06/19 14:00,01:00

Commissioning,2015/06/25 12:00,2015/06/25 10:30,01:30

Deliver/Collect,2015/06/30 08:00,2015/06/30 07:00,01:00

Demo,2015/06/25 10:30,2015/06/25 09:30,01:00

Meeting - P,2015/06/15 13:30,2015/06/15 13:00,00:30

Meeting - P,2015/06/15 15:30,2015/06/15 14:30,01:00

Meeting - P,2015/06/16 12:30,2015/06/16 12:00,00:30

Meeting - P,2015/06/16 15:30,2015/06/16 14:30,01:00

Meeting - P,2015/06/17 12:00,2015/06/17 11:30,00:30

Meeting - P,2015/06/18 16:00,2015/06/18 15:30,00:30

Meeting - P,2015/06/22 16:00,2015/06/22 15:00,01:00

Meeting - P,2015/06/26 12:30,2015/06/26 12:00,00:30

Meeting - P,2015/06/29 14:00,2015/06/29 13:00,01:00

Meeting - P,2015/06/30 15:00,2015/06/30 14:00,01:00

Meeting T,2015/06/08 13:00,2015/06/08 12:00,01:00

Meeting T,2015/06/09 16:00,2015/06/09 14:00,02:00

Meeting T,2015/06/12 16:00,2015/06/12 15:00,01:00

Meeting T,2015/06/15 12:30,2015/06/15 11:30,01:00

Meeting T,2015/06/16 14:00,2015/06/16 13:00,01:00

Meeting T,2015/06/22 13:00,2015/06/22 12:00,01:00

Meeting T,2015/06/22 14:30,2015/06/22 13:30,01:00

Meeting T,2015/06/26 14:30,2015/06/26 13:30,01:00

Meeting T,2015/06/29 08:00,2015/06/29 07:30,00:30

OPD,2015/06/08 11:30,2015/06/08 07:30,04:00

OPD,2015/06/09 13:00,2015/06/09 07:30,05:30

OPD,2015/06/10 12:30,2015/06/10 07:00,05:30

OPD,2015/06/11 11:00,2015/06/11 08:00,03:00

OPD,2015/06/11 16:00,2015/06/11 12:00,04:00

OPD,2015/06/12 12:30,2015/06/12 08:00,04:30

OPD,2015/06/15 11:00,2015/06/15 07:30,03:30

OPD,2015/06/16 12:00,2015/06/16 07:30,04:30

OPD,2015/06/17 11:30,2015/06/17 07:30,04:00

OPD,2015/06/17 16:00,2015/06/17 13:00,03:00

OPD,2015/06/18 15:30,2015/06/18 13:00,02:30

OPD,2015/06/19 13:00,2015/06/19 07:30,05:30

OPD,2015/06/22 12:30,2015/06/22 07:30,05:00

OPD,2015/06/23 13:00,2015/06/23 07:00,06:00

OPD,2015/06/24 10:00,2015/06/24 08:00,02:00

OPD,2015/06/24 12:00,2015/06/24 10:00,02:00

OPD,2015/06/24 22:00,2015/06/24 10:00,12:00

OPD,2015/06/25 15:30,2015/06/25 13:00,02:30

OPD,2015/06/26 12:00,2015/06/26 08:00,04:00

OPD,2015/06/26 16:00,2015/06/26 15:00,01:00

OPD,2015/06/29 13:00,2015/06/29 09:00,04:00

OPD,2015/06/30 13:30,2015/06/30 08:30,05:00

OPD,2015/06/30 23:30,2015/06/30 08:30,15:00

];

produces the identical results