Calculate time difference between 2 time stamps excluding weekends and only working hours

[poluvidyasagar]

Hi Qlikview Community,

I am trying to calculate the time difference in minutes between 2 time stamps?I would like to exclude weekends, holidays and consider work hours to be from 06:00AM to 11:00PM (Monday to Friday)?

How do i achieve this? 

Original Data:

OrderNumber	Event	EventTime
1129280	CreatedAssignment	2018-05-07T07:08:21.32
1129280	FinishedAssignment	2018-05-07T07:31:09.917
1129281	CreatedAssignment	2018-05-07T07:08:23.193
1129281	FinishedAssignment	2018-05-08T10:00:30.753
1128368	CreatedAssignment	2018-05-04T13:08:39.627
1128368	FinishedAssignment	2018-05-07T08:57:17.447

I have used the following expression to calculate the time difference:

'=Num(Interval(Only({<Event={'FinishedAssignment'}>}Timestamp(EventTime))-Only({<Event={'CreatedAssignment'}>}Timestamp(EventTime)),'mm'))*24*60'

and this gave me the following table:

OrderNumber	Lead Time(minutes)
1128368	4,069
1129280	23
1129281	1,612

However, it is correct only when the event time for started and finished assignment falls in the same day. However, i would like to exclude the weekends and including working hours for the day?

Desired Result:

OrderNumber	Desired Lead time(Minutes)	Comments
1128368	769	Considering weekdays and work timings only
1129280	23	correct already
1129281	1,193	considering only work timings from 06 am to 11 pm

I am aware of network days. However, i am not able to write an expression to get what i need?

Can someone please help me with this?

I have attached qvw file for your reference.

Thanks,
Vidya

@sunny_talwar  @swuehl  can you help me with this?

[swuehl · Accepted solution]

You can do it like shown in
https://community.qlik.com/t5/QlikView-App-Development/Calculate-hours-between-two-Date-Time-strings...

This code uses input data that shows the start and end timestamps in the same record line.

You can transform your data that shows the timestamps in different lines using e.g. a Group by LOAD statement.

Hope this helps.

Stefan

[poluvidyasagar]

Hi Swuehl,

Thanks for the reply.

This post helps.

I did not want to load the data by grouping in to each line for each event. Because there are so many exceptions and inconsistencies. I have been able to capture that in the front end.

I have referenced the following expression described in the post. It worked perfectly fine for me.

"=Interval(

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

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

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

)"

I have attached the file if anyone wants to use.

Thanks,

Vidya