How to calculate each date in 2 different months

[Max2019]

I would like to calculate weighted values according to dates. How can I do that?
I tried date/time difference and created calendar such as 2019/1/1, 2019/1/31, 2019/2/1, 2019/2/29 for calculation, but can not find a good way… Anyone help!
How can I get Jan, Feb, Mar … dates and the Cal Unit(divided by dates in the month)?

[SimonS]

Hi @Max2019,

I think I need some more explanation to be able to help you. How do you calculate “Unit in Jan” and “Unit in Feb”? Once you have them, the “Cal Unit in Jan” can be calculated with a Math Formula node.

Cheers,
Simon

[Max2019]

Thank you for your reply. The Unit in Jan and Feb will be input by human. I can load the data from excel in KNIME for column A, B, C, D. I want to calculate in KNIME for column E how many day in Jan which is 2019-01-31 (those are string but I can convert to date) minus 2019-01-01 to get 16 then Cal unit in Jan will be 10,000 x 16 / 31 (again 31 is the full month date) = 5161. For Feb, the full month date will be 28 in 2019 but 29 in 2020. H3 will be 20,000 x minus (2019-02-01 minus 2019-02-15) / 28 days = 9677. Actually the values I need to calculate in KNIME is Column E and F. G and H I can easily do.
So the Start will be xx date until end of the month and End will be start of the month until xx date. If I can get those 2 numbers and the days in the month, I can multiple the unit to get weighted numbers.
I really need your help. Thank you in advance.

[SimonS]

Hi @Max2019,

I’ve built you a small workflow that should do what you are looking for:
date_example.knwf (15.7 KB)

I think the caculation of “Feb dates” is inconsistent in your example. In the first row the end date is exclusive, in the second one inclusive. The workflow assumes the end date is exclusive.

Hope this helps you!
Simon

[Max2019]

Hi @SimonS

Thank you very much for your comment. It is helpful but I should share the whole picture.
DateCal_2.xlsx (15.2 KB)
As in this excel,

I want calculate dates in each month. To get this data, I can calculate with my brain but not KNIME…
I need help here.
Then because I have units in each month

and I know the full dates of the month

I can get

FinalValues will be Dates divide FullDates x Units.
datecal.pptx (34.1 KB)

The date does not contradict (in/exclusive) because I want to have the duration.
so the start should come from end of month minus the start date and end should come from the beginning of the month minus the end date and add minus again.

[SimonS]

Well, this will increases the size and complexity of the workflow of course. You can use the Extract Date&Time Fields node to extract the name of the months, so you can create the first table dynamically. Take a look at flow variables, they might be helpful. You should be able to do the whole workflow with basic KNIME nodes. You may need a loop in the end for the calculations.

Hope this gives you some help to get started,
Simon

[Max2019]

I could not use loop. Instead I have written all the patterns.

this is in metanode

In the excel, I double checked that I can get correct answer but I have covered all 78 patterns.
would you show me how to write loop and variable to have better and easier way?
If I will change to year 2021. the effort is huge…
DateCal_20200420.xlsx (38.8 KB)