Calculate hours between two Date/Time strings

[#1]

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

[swuehl · Accepted solution]

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

[#3]

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

[swuehl]

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

[#5]

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.

[rwunderlich]

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

[swuehl]

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

[swuehl]

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

[rwunderlich]

HI Stefan,

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

-Rob

[#10]

Thanks for your help on this one Stefan