Create period based on dynamic date ranges

[jmpulpeiro]

Hello, nice to find this community for this great tool that I am learning about.
I have been looking for 2 days how to create the variable “Cut-off period” in another column, that period is determined based on a “date” field that each record has, which can be of any month and will be incremental in the future.
The rule to consider is that it is not a calendar month, but from the 26th of one month to the 25th of the following month is the short period
Example1: 14-Nov-2022 belongs to Nov-22
Example 2: 27-Nov-2022 belongs to Dec-22
Example 3: 27-Dec-2022 belongs to Jan-23
In example three, in addition to the month, the year changes
I hope I have explained myself, I thank you in advance for any suggestion or help.

[takbb]

Hi @jmpulpeiro , and welcome to the KNIME community!

I’ve made the assumptions that your dates are currently held as Strings, and so the first thing I’d do is convert them into dates so that we can easily determine the day of the month, and then based on the day compared with the “cutoff” day for each period, decide whether to add a month.

If your data is already in Date format, then the first step can be skipped.

So this was my initial starting data, held as Strings

And this is the workflow I used:

First off, turning the Strings into Dates… I used the format mask d-MMM-yyyy
A single d allows the day of month to be specified as either 1 or 2 digits, so is more flexible than dd, allowing for single digit days to have an optional leading zero.

Secondly… a quick aside…, I used a locale of en-US in that conversion. That may need to change depending on your data. I interchange en-GB and en-US for dates with the big difference between the two being what happens with September! The latest releases of Java insist that in UK, using en-GB we write the September “3 letter” abbreviation as “Sept” whereas for the en-US locale, it is the more logical “Sep”. So although I’m in the UK, my locale choice tends to vary as I like my date data to be standardised on length!!

Once we have the item date as a Date, we can then easily grab just the day of the month using the Extract Date&Time Fields.

We then need to decide if a month needs to be added. From your rules, what I have called the “cutoff” date is 26, which represents the start of the new period. So any days between 26 and the end of the month should go into next month. This is handled by a Rule Engine, which determines an “offset” that we need to use to add to the month:

Date&Time Shift can then add the “month_add” offset to the current date, and finally Date&Time to String can return us our new period “label” using the format MMM-yy. And again, I have used the en-US locale so September will be 3 letters e.g. Sep-23

Here is the workflow:
Dates to Periods based on cutoff day.knwf (20.3 KB)

[takbb]

This one is especially for you @gonhaddock because I suspect I know what you are thinking,

String Manipulation:

string(	
	indexOf($ItemDate$,"-")==2
	  ?toInt(substr($ItemDate$,0,2))>=26
		
		?join(substr("FebMarAprMayJunJulAugSepOctNovDecJan",
						indexOf("JanFebMarAprMayJunJulAugSepOctNovDec",substr($ItemDate$,3,3)),
						3),"-",
				string(toInt(substr($ItemDate$,9,2))+
					toInt(substr("Jan0Feb0Mar0Apr0May0Jun0Jul0Aug0Sep0Oct0Nov0Dec1",
						indexOf("Jan.Feb.Mar.Apr.May.Jun.Jul.Aug.Sep.Oct.Nov.Dec.",substr($ItemDate$,3,3))+3,1)))
				)	
		: join(substr($ItemDate$,3,4),substr($ItemDate$,9,2))
	  :join(substr($ItemDate$,2,4),substr($ItemDate$,8,2))
	
)

or something like that anyway (use at own risk - not fully tested, lol!)

[gonhaddock]

Hi @takbb
Lol, I feel overwhelmed now
BR

[jmpulpeiro]

Incredible answer @takbb, thank you very much for doing it in such a detailed way, I’m starting to implement it.
I hope I can learn enough to, in the future, also collaborate with other people who have doubts about implementing.
Thank you again in advance and have a great week.