Date Assignment

Hi,

I’m new to Knime and I am trying to figure out how to assign dates to billings data I am using. The dates are written in my data under Fiscal_Week column as fiscalyearfiscalweek (Ex: 201901; First week of our fiscal calendar which corresponds to first week of October 2019). I tried to create date ranges with 7 day intervals as my data was collected on a weekly interval. I just don’t know how to assign the dates I’ve created to my data based on the Fiscal_Week column to make sure all the correct weekly interval dates are being assigned to the corresponding fiscalyearfiscalweek value.

Is there anything I can do to assign the values from the date table I have created to my data based on the fiscalyearfiscalweek value? Am I going about this the wrong way?

Any help would be much appreciated. Thanks!

Hi @lt476

Welcome to the Knime Community!

I’ve made the next workflow


which in the end produces this table
afbeelding
Maybe this first date is not exactly as you need it, but you can tweak this in the node which has a comment “fix startdate”.

This is the workflow 25466.knwf (33.6 KB)

2 Likes

Thanks @JanDuo!

This was really helpful, there’s just one last issue I’m having. The fiscal date column seems to switch from a weekly granularity to daily in the 53rd week of any year (years with only 52 weeks don’t have this problem). Any idea how I could fix this and maintain the weekly granularity for every date?

Thanks.

Hi @lt476
Iḿ not sure if I understand your issue correctly. When I add more rows I get this.
image

This shows also how simplified my solution was: it even allows a week 54 (which still counts for 7 days).

This workflow does not cater for all possible years, because I don’t know the rules when a fiscal year starts (in Europe this aligns with the calendar year, I guess you’re somewhere across the ocean from my point of view). I’m used to weeknumbers of a calendaryear where week 1 contains the first Thursday of the year. So sometimes week 1 of a year start in the year prior to it, sometimes week 53 ends in the next year.
How are the rules for fiscal years?

Hi @JanDuo,

The fiscal calendar I’m using must start on a Sunday for each new fiscal year.

Here’s a picture of what my table is showing and the problem I’m having: image
Row 53 (Week = 0) is showing a date only one day after the W52 date of 2017-09-24 whereas I want it to keep using the weekly interval and show 2017-10-01

Hopefully that makes it a bit more clear. Thanks!

Hi @lt476
The problem was that you need this for many years and the actual date of the first Sunday of October changes year by year.
So I created a new workflow which uses this year to determines the first Sunday of October too: 25466-v2.knwf (57.5 KB)

The result now looks like this, where the right column is the data you are looking for:

As you can see there is not check on whether week 53 actually exists in a year, because 201853 and 201901 both give the same date, which is 2019-10-06 (the first Sunday in October).
But I assume your input will never provide such a weeknumber if it didn’t exist.

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.