topic Re: Networkdays Calculation - Exact Decimals Required in QlikView

Networkdays Calculation - Exact Decimals Required

gileswalker — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community Forum,

Hope you can help with this one please?

I have what I thought was a simple query, but after scouring the community page, no-one else seems to be asking this one.

I work in a business where we want to calculate the exact number of working days between a 'StartDateTime' and an 'EndDateTime', taking holidays into account. However because this is driving KPI reporting, we want to be exact. Networkdays isn't doing it for me....here are some details of my experience.

Here is how I currently approached it. Due to the requirement to manage weekdays and holidays, I started with the following function:

NetWorkDays(date_ordered, DateCompleted, $(vStatHols)) as WorkDaysToComplete

....however of course, I only get a whole number returned using this function.

Example - one order looked like this

date_ordered = 5/01/2015 11:51:36 AM

DateCompleted = 7/01/2015 10:28:13 AM

Networkdays = 3

The actual correctly calculated working days is 1.942 days.

The difference from a KPI performance perspective is quite significant (1.058 days) and it magnifies once you spread that logic across a year of order processing and customer reporting.

I will also need the function to round up and down the exact result.........so in the above, it would of course be 2 days.

If anyone can assist me in how I can achieve this, I would really appreciate it. I don't know what other function will allow the calculation of working days and holidays other than networkdays. Hoping there is an easy solution to get the exact decimal.

Thanks

Giles

Re: Networkdays Calculation - Exact Decimals Required

Anonymous — Thu, 19 Jan 2017 06:44:17 GMT

Hi Giles,

Network days gives difference in days only not with decimal points.

If you are looking for exact days difference with decimal points than you should go with datediff or simple date1-date2 formula.

Thanks,

Pooja

Re: Networkdays Calculation - Exact Decimals Required

gileswalker — Thu, 19 Jan 2017 18:12:04 GMT

Thanks Pooja, but this is not a workable solution for my requirement. I am using QlikSense, so the datediff function will not work there, and the simple date1-date2 scenario doesn't dynamically take weekends and holiday dates into account.

Re: Networkdays Calculation - Exact Decimals Required

vishsaggi — Thu, 19 Jan 2017 18:15:11 GMT

Did you try using Num(yourexpresssion, '#0.00')

Re: Networkdays Calculation - Exact Decimals Required

gileswalker — Thu, 19 Jan 2017 18:22:29 GMT

Thanks for your suggestion, however on testing this doesn't work either. It still returns the same number of working days, just with the decimal on the end, e.g. what was showing as 3, is now showing as 3.00

Re: Networkdays Calculation - Exact Decimals Required

maxgro — Thu, 19 Jan 2017 18:50:43 GMT

Try (exclude the first and the last then add the time part of the first and the last)

NetWorkDays(d1, d2) -2 + (1-frac(d1)) + frac(d2)

Maybe you have add a check for holidays in the bold part

Re: Networkdays Calculation - Exact Decimals Required

gileswalker — Thu, 19 Jan 2017 19:46:42 GMT

Hi Massimo - you are a legend! This is exactly what I need!! Wow! Awesome. Thanks so much.

Just 1 question on the NetWorkDays function, do you think it will be ok to insert my holidays variable, i.e. to go from your example of:

NetWorkDays(d1, d2) -2 + (1-frac(d1)) + frac(d2)

....to:

NetWorkDays(d1, d2, $(vStatHols)) -2 + (1-frac(d1)) + frac(d2)

Thanks again Massimo

Giles

Re: Networkdays Calculation - Exact Decimals Required

maxgro — Thu, 19 Jan 2017 20:20:57 GMT

Yes, I think you're right, add the holidays to the networkdays function.

Re: Networkdays Calculation - Exact Decimals Required

gileswalker — Thu, 19 Jan 2017 21:18:46 GMT

Massimo, mate you are right on the money my friend. Its working - great!!!

Thanks so much.

Re: Networkdays Calculation - Exact Decimals Required

vishsaggi — Thu, 19 Jan 2017 21:20:45 GMT

IF you think few of the replies and of Massimo's are correct mark them helpful.

Re: Networkdays Calculation - Exact Decimals Required

swuehl — Thu, 19 Jan 2017 21:27:06 GMT

I think you might need to consider the case where d1 and d2 are on the same date differently.

And maybe also if d1 or d2 can be a holiday / weekend.

See this more general approach taking business hours into account (which you can simplify a lot if you don't care about business hours):

Calculate hours between two Date/Time strings

Re: Networkdays Calculation - Exact Decimals Required

swuehl — Thu, 19 Jan 2017 21:58:33 GMT

The mentioned simplified version might look like

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) *1 // 24 hours per workday, for all days inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), frac(DT2),0) // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),1-frac(DT1),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-1) // correct for first equals last day

)

Re: Networkdays Calculation - Exact Decimals Required

gileswalker — Thu, 19 Jan 2017 22:56:05 GMT

Thanks Stefan, I will give your simplified version a try.

Based on your earlier link, I just used the below (in bold) and it was almost ok -but there was an inconsistent result when a DateCompleted matched a vHol date. Some calculated correctly but some were wrong.....however all instances of DateCompleted matching weekend dates produced accurate results...

Interval((RangeMin(frac(DateCompleted)) - RangeMax(frac(date_ordered)))) + (NetWorkDays(date_ordered, DateCompleted-1, $(vStatHols)))

Anyway I will look at your simplified suggestion now and let you know how I get on. I appreciate your involvement on helping with this and I will update to "correct answer" once I have concluded it works ok.....

Re: Networkdays Calculation - Exact Decimals Required

gileswalker — Thu, 19 Jan 2017 23:49:20 GMT

Hi Stefan,

I tried your solution, and it doesn't satisfy my requirement, but I think I know why. I implemented the code, and reconciled the scenario where the DateCompleted falls on a weekend. The way I read my data output is that your code calculates the exact period from the date_ordered until the weekend starts, which is not quite what I need, and I think this is where I conclude that my explanation has been deficient in detail. So please let me expand on my requirement, and maybe it will become clearer.

The operation I work in receives client orders and performs a pick, pack & ship function, shipping items to end users/retailers.

Orders are only RECEIVED during Monday-Friday weekdays (causing date_ordered to be triggered), and orders are normally closed (triggering DateCompleted) during those week days too - this closure may be on the same day or the next day (etc) as the date_ordered, dependent on the desired service. I am measuring the period between date_ordered and DateCompleted, and need the exact period of time in days, not just Networkdays.

If the order is to be closed the next working day, it must be noted that Friday's orders would be closed on the following Monday, requiring Saturday and Sunday to be excluded from the working days calculations. I also have to consider known holidays which can further extend the order processing period. HOWEVER, exceptionally busy periods could see the business require staff to work weekends and even holiday dates to close those orders, so it is possible to see DateCompleted during weekend and holiday dates.

So the function needs to cover both scenarios ie:

Where the orders are closed during week days (some spanning a weekend)
Where orders are closed on weekends and holidays.

If you are able to help further I would appreciate it - I am finding this quite complex to resolve.

Re: Networkdays Calculation - Exact Decimals Required

gileswalker — Mon, 23 Jan 2017 01:46:46 GMT

After deliberating the best means to move forward, I decided to change tactic as the formulas were getting too complex for me.

I also discovered that I had some orders with date_ordered dates that were not on weekdays which made things more complex still.

So I decided that the best thing to do was to 'normalise' the transaction dates, using the 'lastworkdate' function inside an IF statement, effectively pushing all the transactions that happened on weekends of holidays into the next available working day. That way I could make a more simple formula to get my result.

For anything with date_ordered on weekends, the IF statement and 'lastworkdate' function were used to re-stamp those dates as the next working day at 8am i.e:

Date(LastWorkDate(date_ordered+1, 1,$(vStatHols)) + time('08:00:00', 'hh:mm:ss TT'), 'D/MM/YYYY hh:mm:ss TT') as date_ordered_Corrected

To support this I also used:

Weekday(date_ordered) as date_ordered_Name

Then I used a preceeding load to calculate this:

If(Match(date_ordered_Name, 'Sat', 'Sun') >0, date_ordered_Corrected, If(Match(date_ordered, $(vStatHols)) > 0, date_ordered_Corrected, date_ordered)) as Final_Corrected_Date_Ordered

I also did basically the same for anything 'DateCompleted', although the 'time' function added in was timestamped as the below, meaning that the 'adjusted' DateCompleted was just inside the next available working day:

+ time('00:00:01', 'hh:mm:ss TT')

Then in another preceeding load I scripted the below which gives the exact period of days with any relevant decimals:

Interval((RangeMin(frac(Final_Corrected_DateCompleted)) - RangeMax(frac(Final_Corrected_Date_Ordered)))) + (NetWorkDays(Final_Corrected_Date_Ordered, Final_Corrected_DateCompleted-1, $(vStatHols))) as WorkDaysToCompleteExact

I am pleased to report that this provides the correct data per my requirement.

Re: Networkdays Calculation - Exact Decimals Required

ellyodelly — Thu, 25 Jan 2018 15:52:44 GMT

Massimo- I know this is a year old- but can/would you explain what the -2 is doing? and if the time is in d1 and d2 why should I add it back in?

NetWorkDays(d1, d2) -2 + (1-frac(d1)) + frac(d2)

Re: Networkdays Calculation - Exact Decimals Required

cspindley — Fri, 18 May 2018 15:10:29 GMT

thanQ for your help with this,

Clive Spindley

NHS

Re: Networkdays Calculation - Exact Decimals Required

Keitaru — Sat, 02 Feb 2019 10:50:11 GMT

Having the same issue however for my case would need to include weekends into the equation as well.

Re: Networkdays Calculation - Exact Decimals Required

vsap2000 — Tue, 07 May 2019 15:24:25 GMT

Even I am trying to understand the logic behind the expression.

Re: Networkdays Calculation - Exact Decimals Required

crispali — Thu, 28 Nov 2019 09:41:47 GMT

NetWorkDays([Start Datetime], [EndDatetime] -IF(NetWorkDays([Start Datetime], [Start Datetime])>0,1,0)*interval([Start Datetime] - Floor([Start Datetime]), 'd') -IF(NetWorkDays([End Datetime], [End Datetime])>0,1,0)*interval(Ceil([End Datetime]) - [End Datetime], 'd')

Assuming [Start Datetime] <= [End Datetime],
would the above work? My approach is to minus the "tail end" hours of the period from the network days.