Working Days/Network Days between two dates

There is an R library that can do business day calculations in various forms. You might have to adjust how the package handles the start and end day and what to do if the start or end day is a holiday. Also the actual holidays seem to be derived from a localised list - you could also adapt that or set your own list (I was not able to turn the list off completely). And also for your negative examples there seem to be differences in the calculation method.

kn_example_bizdays.knwf (1.9 MB)


library(bizdays)

cal <- create.calendar(name='MyCalendar', weekdays=c('sunday', 'saturday'),
                       adjust.from=adjust.next, adjust.to=adjust.previous, financial = FALSE)

knime.in$aging_r <- bizdays(knime.in$"Net due dt", knime.in$"Calculation Date", 'MyCalendar')

knime.out <- knime.in
2 Likes

Hi mlauber71,

Thank you for your solution.

I did find another solution with Python in one of the above links. since both of these solutions has dependency on other applications (though they are open source), i thought it would be better to do in KNIME itself.

I will definitely try this and see if this works.

Regards,
Pavan.

1 Like

Maybe you could share the Python solution for further reference. I thought about a pure KNIME solution but was not successful as of now.

Were it not for the “holidays” which seems to require importing calendrical information, Java 8 has a java.time package which makes this is pretty straight-shot using the Java Snippet node.

1 Like

@mlauber71: It is available in the 1st link that provided above.
For quick reference, i am updating the link here.
https://het.as.utexas.edu/HET/Software/Numpy/reference/generated/numpy.busday_count.html

@quaeler: Thanks and appreciate your suggestion, i looking to do it with KNIME itself, unless it is highly impossible/time consuming/performance issue.

Assistance from anyone is welcome.

Regards,
Pavan.

In this thread there is a KNIME workflow to extract a list of holidays for different countries from a webpage

And also there are is an example how to shift a target date depending on several factors. It is very much possible to adapt that approach.

Where I was not successful yet is how to construct a calendar from a start and an endpoint. Not sure if that would mean one has to use a somewhat dirty loop.

Attached is a workflow which does it all in KNIME - it uses a pretty questionable, US-only, “is this a business day method” that i cribbed from GitHub (cited in the Java Snippet code.)

Apparently Ruby (i throw up in my mouth) has a gem that does this sort of thing really well ( https://github.com/holidays/holidays ) and there’s an online service ( https://holidayapi.com/ ) which does this really, really, well - but is for-pay.

At any rate, hope this helps.

pavan’s working days.knwf (7.5 KB)

ps. no assertions that this is well written Java code - just gets the job done.

Hi there!

Here it is in KNIME and KNIME only :smiley:

This sounds easier than it looks like! But still not too bad. Note that this is not efficient way cause 1 row is one loop iteration. I only read 10.000 rows in attached workflow. For that execution is around 5 minutes.

Take a look and if any question feel free to ask.

2019_04_04_Number_of_Working_Days_Between_Two_Dates.knwf (1.7 MB)

Br,
Ivan

4 Likes

@ quaeler: i have tried this, the output data is not matching with the actual aging, not sure if am missing something. once again thanks for your assistance.

Regards,
Pavan

Hi Ivan,

I have tried the workflow shared by you, this seems to be some complicated for me (its just my opinion). However as mentioned by you, it is not efficient way to get the required output. I think it would be wise to use other suggestions that we have like java, R, Python, etc.

I don’t know java, i did tried with the workflow shared by @mlauber71 and i have the below error screen shots. I did check in R application and library (bizdays) is available.

Regards,
Pavan.

Sorry - i’m not clear on what “aging” is supposed to mean - i see the value, but it’s still not clear how it relates what i am calculating. I’ve augmented my workflow to also generate a column that is “weekdays ignoring holidays” - so basically, if it is not a Saturday or Sunday then it is counted.

Even with this new column, the ‘aging’ column is sometimes (this +/- 1), sometimes (this), sometimes (this +/- 2), … very rarely 3 or 4 off… very much not clear. (I’ve also now made a delta column).

Maybe if you could explain what “aging” is, we could better solve this; if this value does incorporate locale-specific holidays, then you would need to encode the date logic for those yourself in the appropriate method in the Java Snippet node.
pavan’s working days.knwf (7.7 KB)

1 Like

Could you check if you can run R snippets in general.

Then make sure you have the latest version of R set up and running following these hints:

@quaeler: The aging column is the actual aging calculated in excel for reference. The output from the KNIME workflow should match to it. The aging can be +/- also. Right now i am in a situation where i do not have any knowledge on java and not able to apply to the workflow.

@mlauber71: i have very very basic knowledge on python and R, i am just trying with both of the programs and see how that can be integrated with KNIME from past 2 days. could you suggest on how to run either R or Python general snippets in KNIME.

Regards,
Pavan.

I can give a few links about Knime and R. I would suggest you start with the installation and then use your question here and then move on. For python and KNIME I also have a few links. But my impression is the setup with KNIME is not so straightforward. I might have a second installment about that.

Installation of R

Video tutorial KNIME and R

Basic usage of R and KNIME

The collection of R nodes

(Below every description there are links to sample workflows)

2 Likes

@mlauber71: Thanks for the information, i will spend some time to understand R & Python and see how they can be interpreted to KNIME.

Regards,
Pavan.

When you say “the actual aging calculated in excel for reference,” that doesn’t explain how to get a value for “aging”, and if i open your .XLSX in Excel, i see that those columns values are simply numbers and not a formula.

Without an explanation of how one calculates “aging,” you will have a definite problem writing code or cobbling together nodes to, well, calculate aging.

1 Like

here is the formula used to calculate the aging. i have re-attached the file for your reference.

image
Calculate Aging.xlsx (2.7 MB)

Regards,
Pavan.

Ok - after reading the function definition at https://support.office.com/en-us/article/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7 and seeing that you’re not using a holiday specifying version of it, it was straightforward.

Attached is a workflow with a java snippet node that performs the calculation.

I’ve tried to make it as usable as possible for people who don’t know how to write Java, and so it doesn’t actually rely upon the variable names for the incoming columns, per se (tech: it picks them up via Java Reflection) - they must just be the first two columns and added to the node.

So if you attach the Java Snippet node to input from a different node, or the same node but whose XLSX file you read has different column names, just make sure the data columns are the first two columns and then remove and re-add the columns as seen in the attached GIF (which isn’t attached because the forum software wasn’t having it - see here: http://theori.st/take_away/knime/pavan_node.gif )

pavan’s working days.knwf (8.1 KB)

1 Like

Hi @quaeler

I have followed the steps mentioned in the .gif, received the following errors as mentioned in the below screen shots. I am just guessing some setting related to localdatetime, not sure how to fix it. But i feel once this works it would be a great achievement.




Regards,
Pavan.

What version of KNIME are you running?

Oh how weird… your nodes / version of KNIME / something … create Java types of LocalDate whereas for me, i get columns of Java types LocalDateTime (and so i also get the import statement for that class.) Are you reading in the exact same XLSX file that you uploaded to this thread?

This will sound like a pain in the ass, but everywhere you see LocalDateTime in that Java Snippet code, change it to LocalDate - which should be lines 36, 37, 44, 49, and 67.
Then on line 69, change it to:

	out_totalcalendardays = new Long(ChronoUnit.DAYS.between(dates[0], dates[1]));