topic Re: Calculate hours between two Date/Time strings in QlikView

Calculate hours between two Date/Time strings

Fri, 24 Aug 2012 10:44:51 GMT

Hi,

I was wondering if someone would be able to help me with this query. I am new to Qlikview and have created a straight chart where all my information goes into. I have created a new column which I called Time (log-close).

In that new column I want to be able to calculate in hours, the difference between a field called closedate(which is a date/time field 01/01/2010 09:00:00) and a field called logdate (which is also a date time field 01/01/2010 09:00:00).

To calculate the difference I used the following expression: Interval (closedate - logdate), which gave me the correct hours:minutes:seconds.

However our business hours are only between 08:00 - 18:00. Therefore if we had the example 1 below, at the moment the hours would show as 49:00:00. However as we are not interested in time before 08:00 and after 18:00, the correct time should show as 20:00:00.

To make matters more complicated we are also not interested in weekends, therefore in example 2, the way we currently have it the hours would show as 66:00:00 (as the dates are over the weekend), however the correct time should show as 04:00:00 (as this will ignore times after 18:00 and also the whole of Saturday and Sunday).

Is there a way this can be done?

Example 1:

Logdate - 01/08/2012 09:00:00

Closedate - 03/8/2012 10:00:00

Example 2:

Logdate - 03/08/2012 15:00:00

Closedate - 06/08/2012 09:00:00

Re: Calculate hours between two Date/Time strings

swuehl — Fri, 24 Aug 2012 11:59:44 GMT

Your interval seems to be static, i.e. not depending on selections, so I would implement the calculation in the script.

This could look like:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM, 7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;

drop table TMP;

In addition to the weekends, you can also exclude holidays from the interval calculation, by adding your holidays to the vHol variable (you can create this variable from a holiday table, there are some posts here on how to do this.

I then create a record per date in each interval and assign the start and end times per date.

Finally, just sum up all per-date-intervals and you're done.

You need to adjust the timestamp format to your format used, check the format codes in the Help if needed.

Hope this helps,

Stefan

Re: Calculate hours between two Date/Time strings

Tue, 28 Aug 2012 08:56:41 GMT

Hi Stefan,

This seems to work perfectly. I ran the data you entered and put it on a test qlikview documents, however the only time that didnt calculate was:

8/9/12 11:08 AM,8/9/12 2:57 PM

For some reason this doesn't show a time at all, yet it is not a holiday.

Regards,

Jon

Re: Calculate hours between two Date/Time strings

swuehl — Tue, 28 Aug 2012 11:16:53 GMT

The numerical representation of that date is 41130, and I have included that date to the vHol variable, for testing.

Might also be a holiday somewhere in the world.

Regards,

Stefan

Re: Calculate hours between two Date/Time strings

Tue, 28 Aug 2012 13:17:17 GMT

Hi Stefan,

Thanks for the information. I have just tried to set this up so it brings through the logdate and closedate from SQL. I am assuming that it pulls through a field called Duration, however this Duration field does not have any information in it.

Re: Calculate hours between two Date/Time strings

rwunderlich — Tue, 28 Aug 2012 18:17:08 GMT

Hi Sefan,

I like your script but it seems a simpler solution may be the single expression:

=Interval(

(RangeMin(frac(Closedate), MakeTime(18))

- RangeMax(frac(Logdate), MakeTime(8)))

+ (NetWorkDays(Logdate, Closedate-1) * MakeTime(10)) // Only 10 hours per whole day

)

Works in both charts and script. NetWorkDays() optionally supports holidays as well. I haven't extensively tested this but it seems right.

-Rob

Re: Calculate hours between two Date/Time strings

swuehl — Wed, 29 Aug 2012 16:04:10 GMT

Hi Rob,

that is indeed a much simpler solution.

I must admit that I posted above script while working on http://community.qlik.com/message/251011

where I initially followed a similar approach than you, but the request in that thread was to include saturdays.

It's a pity that networkdays function does not allow to specify a different weekend pattern (and also some other functions).

Or have I missed something here?

But you're right, given the request in this thread, we can use networkdays and I should have pointed that out.

We might need to add a check for weekend/holidays on the first / last date in the period (though this case might be excluded by business rules).

If you apply your neat expression to my sample data, you'll see that you get different results than me for those cases, where either Closedate or Logdate (or both) are a weekend / holiday.

You are also getting different results for cases when both Closedate and Logdate are working days, but the logged time is outside working hours (this case may also be excluded by business rules, but I personally encountered many situations where the Logdate could happen any time).

I am attaching a sample file to demonstrate these cases.

Maybe we can use something like this to handle these cases (if needed):

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)

// 10 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day

)

as DurationStefan

If these checks are not needed, I think your expression is really most simple and nice.

Regards,

Stefan

Re: Calculate hours between two Date/Time strings

swuehl — Wed, 29 Aug 2012 16:10:20 GMT

Jonathan,

if you read in your timestamps, you need to ensure that QV interpretes these values as such. Your timestamps in QV must have a numerical representation to make all these suggested expressions / scripts work.

That means, if you are using num(Closedate), that must return a numerical.

To help QV with interpreting your timestamps, you can use timestamp#(FIELD, FORMATCODESTRING) function when reading in from DB. Please check the format codes in the Help.

Or just set the format code used as standard timestamp format:

Maybe use this in your script (I am using the format of your OP):

Set TimestampFormat = 'DD/MM/YYYY hh:mm:ss';

Hope this helps,

Stefan

Re: Calculate hours between two Date/Time strings

rwunderlich — Wed, 29 Aug 2012 17:49:12 GMT

HI Stefan,

Thanks for identifying the limitations of my solution. It was a bit simplistic.

-Rob

Re: Calculate hours between two Date/Time strings

Thu, 30 Aug 2012 09:06:45 GMT

Thanks for your help on this one Stefan

Re: Calculate hours between two Date/Time strings

Thu, 30 Aug 2012 09:07:00 GMT

Thanks for you help on this one Rob.

Re: Calculate hours between two Date/Time strings

Wed, 02 Jul 2014 22:21:36 GMT

Hi!

How can i adapt this script adding a interval to lunch in 12:00 for 13:00?

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)

// 10 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day

)

as DurationStefan

Thanks!

Re: Calculate hours between two Date/Time strings

swuehl — Sun, 06 Jul 2014 16:56:19 GMT

Maybe like in this post:

Re: Expression with Working Days and working Hours

Re: Calculate hours between two Date/Time strings

Mon, 07 Jul 2014 16:45:38 GMT

Tkanks for your answer!

I used this script like below, but it have a some problem.

In case de dates are for example DT1 28/05/2014 08:49:18 DT2 28/05/2014 09:09:09 the answer is 01:19:51 instead 19:51. Any idea?

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(9) //8 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), Rangemin(rangemax(frac(DT2),maketime(13)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(13)),maketime(13))

+Rangemin(rangemax(frac(DT2),maketime(8)),maketime(12))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(12))

+Rangemin(rangemax(frac(DT1),maketime(12)),maketime(12))-Rangemax(rangemin(frac(DT1),maketime(12)),maketime(8)),0) // working first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(9)) // correct for first equals last day

))

Thanks!

Re: Calculate hours between two Date/Time strings

swuehl — Fri, 11 Jul 2014 17:38:56 GMT

There is a small error in the calculation of working hours first day:

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(9) //9 hours per workday, for all day inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), Rangemin(rangemax(frac(DT2),maketime(13)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(13)),maketime(13))

+Rangemin(rangemax(frac(DT2),maketime(8)),maketime(12))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(13))

+Rangemin(rangemax(frac(DT1),maketime(12)),maketime(12))-Rangemax(rangemin(frac(DT1),maketime(12)),maketime(8)),0) // working first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(9)) // correct for first equals last day

)) as DurationBreak

Re: Calculate hours between two Date/Time strings

Tue, 09 Sep 2014 12:12:02 GMT

Hi Stefan,

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,7/6/12 4:38 PM

6/29/12 4:45 PM, 7/6/12 4:19 PM

8/1/12 09:00 AM, 8/3/12 10:00 AM

8/3/12 03:00 PM, 8/6/12 09:00 AM

];

TMP:

LOAD ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(8)),maketime(18)), maketime(8)) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(8),rangemin(frac(DT2),maketime(18))),Maketime(18)) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT)

LOAD

ID,

interval(sum(End-Start)) as Duration

Resident TMP where WeekDay(Date)<5 and not match(Date,$(vHol)) group by ID;

Re: Calculate hours between two Date/Time strings

Anonymous — Fri, 25 Sep 2015 10:47:30 GMT

I have the near about the same requirement.

I have gone through your suggestion but i am not able to implement. I am very sorry.

Can you help me on that?

Re: Calculate hours between two Date/Time strings

Anonymous — Thu, 01 Oct 2015 07:08:02 GMT

Hi Stefan,

Your Solution (the one without Lunch Breaks) works well but I have a small bug that I cannot seem to solve. The working hours is 08:00 to 17:00 so it works with most but I have for example a ticket that came in 17:11 and closed 17:28. It shows a 01:00 in my results. I have pulled apart the calc but it still does not work.

Any idea? attached is the script snippt and a screenshot of the timestamp and results

Interval(
rangesum(
NetWorkDays(TicketSubmittedDate+1,TicketAcceptedDate-1,$(vHol)) * MakeTime(8)
,if(NetWorkDays(TicketAcceptedDate,TicketAcceptedDate,$(vHol)),Rangemin(rangemax(frac(TicketAcceptedDate),maketime(8)),maketime(17))-Rangemax(rangemin(frac(TicketAcceptedDate),maketime(8)),maketime(8)),0) // working hours last day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)),Rangemin(rangemax(frac(TicketSubmittedDate),maketime(17)),maketime(17))-Rangemax(rangemin(frac(TicketSubmittedDate),maketime(17)),maketime(8)),0) // working hours first day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)) and floor(TicketSubmittedDate)=floor(TicketAcceptedDate),-MakeTime(8)) // correct for first equals last day
)) as ResponseTime

Thanx

Re: Calculate hours between two Date/Time strings

swuehl — Thu, 01 Oct 2015 13:57:13 GMT

8:00 to 17:00 are 9 hours, right?

Interval(
rangesum(
NetWorkDays(TicketSubmittedDate+1,TicketAcceptedDate-1,$(vHol)) * MakeTime(9)
,if(NetWorkDays(TicketAcceptedDate,TicketAcceptedDate,$(vHol)),Rangemin(rangemax(frac(TicketAcceptedDate),maketime(8)),maketime(17))-Rangemax(rangemin(frac(TicketAcceptedDate),maketime(8)),maketime(8)),0) // working hours last day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)),Rangemin(rangemax(frac(TicketSubmittedDate),maketime(17)),maketime(17))-Rangemax(rangemin(frac(TicketSubmittedDate),maketime(17)),maketime(8)),0) // working hours first day
,if(NetWorkDays(TicketSubmittedDate,TicketSubmittedDate,$(vHol)) and floor(TicketSubmittedDate)=floor(TicketAcceptedDate),-MakeTime(9)) // correct for first equals last day
)) as ResponseTime

Re: Calculate hours between two Date/Time strings

sunilkumarqv — Fri, 18 Mar 2016 11:08:58 GMT

HI Jonathan ,

we are facing similar kind of format text but want us a 24 hours with excluding weekends instead of working hours.

please help me