Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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?
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.
You can try use an if statement.
if (Date2<Date1, networkdays(Date1,Date2)*-1, networkdays(Date1,Date2))
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?
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 |
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
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