Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in.
**SIGN UP NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Networkdays Calculation - Exact Decimals Required

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

gileswalker

Creator

2017-01-18
07:01 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

- « Previous Replies
- Next Replies »

1 Solution

Accepted Solutions

gileswalker

Creator

2017-01-19
02:46 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 + *

Thanks again Massimo

Giles

23 Replies

Anonymous

Not applicable

2017-01-19
01:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

7,137 Views

gileswalker

Creator

2017-01-19
01:12 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

7,137 Views

vishsaggi

Champion III

2017-01-19
01:15 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

7,137 Views

gileswalker

Creator

2017-01-19
01:22 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

7,136 Views

maxgro

MVP

2017-01-19
01:50 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2017-01-19
02:46 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 + *

Thanks again Massimo

Giles

maxgro

MVP

2017-01-19
03:20 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

gileswalker

Creator

2017-01-19
04:18 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Thanks so much.

7,136 Views

vishsaggi

Champion III

2017-01-19
04:20 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

7,136 Views

- « Previous Replies
- Next Replies »