Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
khaycock
Creator
Creator

Work Week

Is there any way to exclude all Saturday's and Sunday's from my date to make it a Monday-Friday work week?

I have two date fields and I want to calculate the difference between them as a plus or minus working days

Planned Date - Actual Date AS Working Days

At the moment it works fine but will sum all days when I want just Monday-Friday to be calculated

Labels (1)
  • week

1 Solution

Accepted Solutions
awaisqureshi
Contributor III
Contributor III

Glad you got it to work, to overcome that issue I would add a plus/minus 1 to you if statement.  See the changes in bold below. Hope that gets you what you need!

if(ActualDaysDifference = '0', ActualDaysDifference,if(ActualDeliveryDate<PlannedDeliveryDate, networkdays(ActualDeliveryDate,PlannedDeliveryDate)*-1 + 1, WorkingDaysDifference - 1)) AS DaysDifferenceAgain

View solution in original post

10 Replies
awaisqureshi
Contributor III
Contributor III

Hi @khaycock

 

You an use the NetWorkDays() function to get what you're looking for. See more in the thread below.

https://community.qlik.com/t5/QlikView-App-Development/Date-Diff-Working-Days-Only/td-p/172344

khaycock
Creator
Creator
Author

How do I use this in an expression?

I have two date fields Date 1 and Date 2 and I want to know the difference between them in working days.

I'm not sure how to use the network days function with a subtraction?

awaisqureshi
Contributor III
Contributor III

The net work days function will do the subtraction for you.  Lets say date1 is your start date and date2 is your end date then you can use the following expression to get the difference in only workdays.  
 
networkdays(date1,date2) as date_diff
khaycock
Creator
Creator
Author

I think I have it working but it doesn't seem to do minus numbers when Date 2 is earlier than Date 1. E.g. I have Date 1 as 7th Jan 2019 but Date 2 as 12th Dec 2018. I would expect the number to be -19 but it displays 0. 

I can swap the two dates around and it gives me +19 but ideally I would have it showing as -19 as it shows the item was delivered 19 days early.

awaisqureshi
Contributor III
Contributor III

You can try use an if statement.

if (Date2<Date1, networkdays(Date1,Date2)*-1, networkdays(Date1,Date2))

khaycock
Creator
Creator
Author

It doesn't like the first part of the IF statement. I get an error for the greater than section for some reason? I tested with a simple..

if([Date 1]<[Date 2], 'Yes', 'No') 

And it still didn't work so I think there is an issue with that part?

awaisqureshi
Contributor III
Contributor III

Depending on the data you probably need an aggregation around the dates. For example using the data below, if you use the expression with Customer as the dimension it would not work because there are multiple date1 and date2 per customer. If you used Order as the dimension then it would work because you have one Date 1 and Date2 per order.

 

Customer

OrderDate1Date2
A11/1/20001/2/2000
A22/1/20002/2/2000
khaycock
Creator
Creator
Author

Okay so I've managed to get it working. However the network days thing counts one of the dates as one day. In the below screenshot you can see its calculated the difference between 4/19 and 4/18 to be -2, when actually I would expect it to be -1.

days.PNG

Is there anyway to change this?

This is the current statement:

if(ActualDaysDifference = '0', ActualDaysDifference,if(ActualDeliveryDate<PlannedDeliveryDate, networkdays(ActualDeliveryDate,PlannedDeliveryDate)*-1, WorkingDaysDifference)) AS DaysDifferenceAgain

awaisqureshi
Contributor III
Contributor III

Glad you got it to work, to overcome that issue I would add a plus/minus 1 to you if statement.  See the changes in bold below. Hope that gets you what you need!

if(ActualDaysDifference = '0', ActualDaysDifference,if(ActualDeliveryDate<PlannedDeliveryDate, networkdays(ActualDeliveryDate,PlannedDeliveryDate)*-1 + 1, WorkingDaysDifference - 1)) AS DaysDifferenceAgain