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

Samuel.
Samuel. Contributor
in Beast Mode

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

Tagged:

Answers

  • Jones01
    Jones01 Contributor
    edited April 2024

    @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.
    Samuel. Contributor

    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
    Jones01 Contributor

    @Samuel. apologies I misunderstood your message.

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

    image.png
  • Samuel.
    Samuel. Contributor

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

    MagicETL Values:

    image.png

    Beastmode Values:

    image.png
  • Jones01
    Jones01 Contributor

    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.
    Samuel. Contributor

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

  • Garrett_Kohler
    Garrett_Kohler Member

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

  • Samuel.
    Samuel. Contributor

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

  • German_Rodriguez
    German_Rodriguez Member

    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:

    1. DAYOFWEEK(simple_timestamp): Returns a number representing the day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday).
    2. 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.
    3. 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.
    4. WEEKOFYEAR(): This calculates the correct week number for the adjusted date (the previous Monday).

Categories