Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gileswalker
Creator
Creator

Networkdays Calculation - Exact Decimals Required

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

1 Solution

Accepted Solutions
gileswalker
Creator
Creator
Author

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

View solution in original post

21 Replies
singhpooja
Creator III
Creator III

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

gileswalker
Creator
Creator
Author

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.

vishsaggi
Champion III
Champion III

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

gileswalker
Creator
Creator
Author

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

maxgro
MVP
MVP

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

gileswalker
Creator
Creator
Author

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

maxgro
MVP
MVP

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

gileswalker
Creator
Creator
Author

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

Thanks so much.

vishsaggi
Champion III
Champion III

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