Working Days/Network Days between two dates

[pawanmtm]

Hi Team,

I would like to calculate aging (network days/weekdays) between two dates, considering Saturday and Sunday as standard weekends. Attached is the data for reference.
Calculate Aging.xlsx (2.1 MB)

I have gone through the below links in the forum, but couldn’t get the required output.
“Count Weekdays/Workdays”
“Calculate the difference between working days and public holidays”
“count weekdays between two dates”

For the last one Iris have provide the solution as “I would first make a list of all days between the two days. Than extract the day of week, filter all Saturdays and Sundays and finally count the remaining using a groupby node.”

I am not sure how do i apply that to my requirement.

Could someone assist on this please.

Regards,
Pavan.

[FabioBusca]

Hi Pavan,
you want todo the calcultation in excel or in knime? if excel you can look for the date&time formulas: I have italian version and the formula is =GIORNI.LAVORATIVI.TOT(cell#1;cell#2)
I hope it help

[pawanmtm]

Hi Fabio,

I would like to do it in KNIME.

Regards,
Pavan.

[mlauber71]

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

[pawanmtm]

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.

[mlauber71]

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

[quaeler]

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.

[pawanmtm]

@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.

[mlauber71]

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

Dividing and splitting rows

I constructed a workflow that illustrates how you could do it. Basically you first do it like in the example above you count down from the original TargetDay the you expand the possible TargetDays by factor 3 (so you will have enough days once you start eliminating some in-between) then you mark all days that are not suitable like holidays and weekends the remaining ones you just join back by an iteration number and voila you have the new TargetDays [19] the yellow marked columns state why …

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.

[quaeler]

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.

[ipazin]

Hi there!

Here it is in KNIME and KNIME only

@pawanmtm

For the last one Iris have provide the solution as “I would first make a list of all days between the two days. Than extract the day of week, filter all Saturdays and Sundays and finally count the remaining using a groupby node.”

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

[pawanmtm]

@ 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

[pawanmtm]

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.

[quaeler]

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)

[mlauber71]

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:

Install R alongside KNIME on Windows and MacOS

Over the last months I have collected a few entries about the installation of R alongside KNIME that contain some maybe useful hints how to overcome certain obstacles while installing them. I want to bring them together here. (If you are looking for a basic guide about how to install R in the first place you might look here or here). A few maybe unnecessary remarks upfront. Yes it takes some effort to get KNIME and R up and running but in the end you will be rewarded with the whole world of pos…

[pawanmtm]

@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.

[mlauber71]

@pawanmtm

could you suggest on how to run either R or Python general snippets in KNIME.

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

Install R alongside KNIME on Windows and MacOS

Over the last months I have collected a few entries about the installation of R alongside KNIME that contain some maybe useful hints how to overcome certain obstacles while installing them. I want to bring them together here. (If you are looking for a basic guide about how to install R in the first place you might look here or here). A few maybe unnecessary remarks upfront. Yes it takes some effort to get KNIME and R up and running but in the end you will be rewarded with the whole world of pos…

Video tutorial KNIME and R

Microsoft R Integration in KNIME

Basic usage of R and KNIME

Basic R Usage – knime

The collection of R nodes

R — NodePit

(Below every description there are links to sample workflows)

[pawanmtm]

@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.

[quaeler]

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.

[pawanmtm]

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

Calculate Aging.xlsx (2.7 MB)

Regards,
Pavan.