# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a \$200 Amazon Gift Card! Watch Video
cancel
Showing results for
Did you mean:
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
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

10 Replies
Contributor III

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

Creator

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?

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
Creator

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.

Contributor III

You can try use an if statement.

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

Creator

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?

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

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

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

Tags