topic Re: Work Week in QlikView

Work Week

khaycock — Sat, 16 Nov 2024 03:11:36 GMT

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

Re: Work Week

awaisqureshi — Tue, 25 Jun 2019 12:43:48 GMT

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

Re: Work Week

khaycock — Tue, 09 Jul 2019 13:19:25 GMT

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?

Re: Work Week

awaisqureshi — Tue, 09 Jul 2019 13:24:53 GMT

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

Re: Work Week

khaycock — Tue, 09 Jul 2019 13:36:48 GMT

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.

Re: Work Week

awaisqureshi — Tue, 09 Jul 2019 13:40:05 GMT

You can try use an if statement.

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

Re: Work Week

khaycock — Tue, 09 Jul 2019 13:48:09 GMT

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?

Re: Work Week

awaisqureshi — Tue, 09 Jul 2019 13:56:16 GMT

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

Re: Work Week

khaycock — Mon, 15 Jul 2019 15:50:39 GMT

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

Re: Work Week

awaisqureshi — Mon, 15 Jul 2019 15:54:27 GMT

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

Re: Work Week

khaycock — Mon, 15 Jul 2019 16:06:30 GMT

This works perfectly, thank you so much for all your help 🙂