Skip to content

    • Beast Mode

    Beast Mode

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

    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:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

    • 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

    • 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.

    • Contributor

      @Samuel. apologies I misunderstood your message.

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

      image.png
    • Contributor

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

      MagicETL Values:

      image.png

      Beastmode Values:

      image.png
    • 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
    • Contributor

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

    • Member

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

    • Contributor

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

    • 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).

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories