Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

poluvidyasagar
Contributor II

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

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:

OrderNumberEventEventTime
1129280CreatedAssignment2018-05-07T07:08:21.32
1129280FinishedAssignment2018-05-07T07:31:09.917
1129281CreatedAssignment2018-05-07T07:08:23.193
1129281FinishedAssignment2018-05-08T10:00:30.753
1128368CreatedAssignment2018-05-04T13:08:39.627
1128368FinishedAssignment2018-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:

OrderNumberLead Time(minutes)
11283684,069
112928023
11292811,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:

OrderNumberDesired Lead time(Minutes)Comments
1128368769Considering weekdays and work timings only
112928023correct already
11292811,193considering 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?

1 Solution

Accepted Solutions
MVP
MVP

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

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

 

View solution in original post

2 Replies
MVP
MVP

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

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

 

View solution in original post

poluvidyasagar
Contributor II

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

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