Work Week

[khaycock]

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

[awaisqureshi]

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]

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]

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

[awaisqureshi]

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.  

[awaisqureshi]

networkdays(date1,date2) as date_diff

[khaycock]

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]

You can try use an if statement.

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

[khaycock]

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]

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	Order	Date1	Date2
A	1	1/1/2000	1/2/2000
A	2	2/1/2000	2/2/2000

[khaycock]

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.

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 · Accepted solution]

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