WEEKOFYEAR is not aligning to ISO-8601, is this correct?

Samuel. · 2024-04-02T09:23:14+00:00

There was an error rendering this rich post.

Hi,

I recently utilised the Week of Year function for a date column. I'm unsure if this is incrementing from 0, but the value for the 12th of Feb 2024 returns 6. I would expect it to return 7. https://www.epochconverter.com/weeks/2024 .

Can someone confirm if this is intended? It looks to use Sunday as the start of the week in this function.

Best,

Sam

Find more posts tagged with

Date

Time

Magic ETL

Card Builder

Beast Mode

Accepted answers

All comments

Jones01

@Samuel.

Have a look at using the function Week and pass into it whatever mode fits your needs.

WEEKOFYEAR(date[,mode])

WEEK(date[,mode])

Both do the same thing.

Modes are

0: First day is Sunday, range is 0-53, first week has a Sunday in this year.

1: First day is Monday, range is 0-53, first week has more than 3 days this year.

2: First day is Sunday, range is 1-53, first week has a Sunday in this year.

3: First day is Monday, range is 1-53, first week has more than 3 days this year.

4: First day is Sunday, range is 0-53, first week has more than 3 days this year.

5: First day is Monday, range is 0-53, first week has a Monday in this year.

6: First day is Sunday, range is 1-53, first week has more than 3 days this year.

7: First day is Monday, range is 1-53, first week has a Monday in this year.

https://domo-support.domo.com/s/article/360044289573?language=en_US

Samuel.

Hey @Jones01,

I'm referring to the weekofyear function above. I'm fully aware of the functionality of both; I'm asking if there's a reason why the values don't align with international standards.

Jones01

@Samuel. apologies I misunderstood your message.

I've tested on my instance and I get back Week 7.

Samuel.

There seems to be an delta between the MagicETL calculation and the Beastmode calculation.

MagicETL Values:

Beastmode Values:

Jones01

this probably covers why

https://community-forums.domo.com/main/discussion/59451/weekofyear-should-be-removed-from-the-approved-function-list-or-its-behavior-should-be-changed

Samuel.

Thanks @Jones01 - weirdly Week() in MagicETL also has the same functionality issue and returns the same value 🤦‍♂️

Garrett_Kohler

@Samuel. Do you have a default fiscal calendar set for your organization?

Samuel.

Hey @Garrett_Kohler, no - this setting is not seemingly enabled or used in our org.

German_Rodriguez

Correct Calculation to Ensure Monday-Sunday Week: (ISO 8601 week numbering system)

WEEKOFYEAR(DATE_ADD('simple_timestamp', INTERVAL -MOD(DAYOFWEEK('simple_timestamp') + 5, 7) DAY))

Explanation:

DAYOFWEEK(simple_timestamp): Returns a number representing the day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday).
MOD(DAYOFWEEK(simple_timestamp) + 5, 7):
- This operation adjusts the DAYOFWEEK() result to account for Monday being the start of the week.
- DAYOFWEEK() starts at Sunday (1), but we need the week to start on Monday (2).
- The +5 ensures the calculation moves the day to match the Monday-Sunday week pattern.
- MOD(..., 7) ensures the result is within a valid range of days (0-6), correctly moving the date to the previous Monday.
DATE_ADD(simple_timestamp, INTERVAL -MOD(DAYOFWEEK(simple_timestamp) + 5, 7) DAY): This subtracts the correct number of days from simple_timestamp to get the previous Monday.
WEEKOFYEAR(): This calculates the correct week number for the adjusted date (the previous Monday).