Extract date&time week similar to Excel

I used the extract data and time fields node and extracted year, month and week from the date field and I realized that there was a bug in this as the week number output for the dates from 29/12/2019 till 31/12/2019 were wrong. Can somebody guide me on this whether I am doing something wrong or there is a bug in Knime analytics Platform itself.

Hi @Rishabh & welcome to the KNIME world,
the week no. is correct, as the week from 29/12 or 30/12 already belongs to the year 2020.

That’s what my Outlook is saying :slightly_smiling_face:
image
(Monday first day of week)

regards, Tommy

1 Like

Hello Tommy,

But in this I have extracted year, month and week from the data that I have from 2018 till 2020.
So, If I choose year 2019 and week as 1 then it counts 29, 30 and 31 December in week 1 of 2019. Which is incorrect.
This I performed after extracting the data in Excel.
if you can then do try the same you will get to know exactly to what I am saying.

Thanku

Hi @Rishabh,

yes, you’re right. Combining year and date (2019 & 1) leads to inclusion of 2019-12-30 in this case. What you need is a “Year-of-week”, which can be calculated by using…
Z_009_day-of-week.knwf (11.3 KB)

image

regards, Tommy

Hello tommy,

I appreciate what you did but this still isn’t solving my problem.
As to what i mean to say is if I want to see what sale did I make in last week of the year, it will give me inaccurate results.
As it will consider 30 and 31 in next year.
And I did not want my data to be incorrect.

test

1 Like

Hi @Rishabh,

I think the year of week is what you need as @tommy said. With version 4.0, we added this option to the Extract Date&Time Fields node so you don’t need an additional workflow to calculate it. See the screenshot. If this option is not available for you, I recommend updating Analytics Platform.

Cheers,
Simon

1 Like

Hello Simon,

Let me share 2 pics, one denotes the output from knime using year and year(week)
and second is from excel, I want that Excel output.
please have a look on both.

Thanku

Capture Capture1

The result of the week number depends on the locale that you are using. I know that for the US and for Germany, the 31st Dec 2019 belong to week 1 of 2020. Which locale are you using in Excel? In KNIME, you can set the local in the dialog, see

I hope this is the cause of your issue. If not, Excel seems to do something wrong in my opinion, and you would need some additional nodes to adapt the week number.

Cheers,
Simon

1 Like

Hello @SimonS

I changed my Locale to de but still this is not what I am looking for
the thing is in my opinion it should display 53 as week for 30 and 31 Dec 2019 but it is showing week 1 of next year.
I want the exact result which is output in excel itself.
I changed the Locale of excel as well and still it is showing me correct result.
But result in Knime is not what I am looking for, I think this node do need some readjustments.

Thanks

Hi @Rishabh,

If you compare the results to what you can find on other platforms, e.g. https://www.calendar-week.org/2020/1/, then you can see that the result of KNIME is correct (for the corresponding locales). Which function do you use in Excel to calculate the week number? I am not an Excel expert, but maybe this function uses a specific locale?

Cheers,
Simon

Hello @SimonS

I used weeknum in excel to find week number.
I get it what you are trying to say, but it still isn’t solving my problem
I will have to look around (maybe create a workflow) for the solution that I need.

thanku

The Excel documentation says this function returns week number 1-54. Interesting, I don’t know of any other system that counts weeks up to 54. However, the Extract Date&Time Fields does not have a functionality to do that, sorry.
I think something like adding 52 to the week number for dates that have different values for Year and Year (week-based), could work. You can use the Column Expressions node with the following expression:

if (not(equals(column("Year") , column("Year (week-based)")))){
    column("Week") + 52
} else {
    column("Week")
}

I guess you would need to further refine the expressions for the edge case of week 54 (1st Jan is a Sunday (or Saturday for locales where the first day of the week is Sunday) in a leap year (?) ).

I hope this helps!
Simon

3 Likes

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