YEARWEEK Function and Modes

bgweis
bgweis Member
in Beast Mode

Hello all -

I am looking for some help with the YEARWEEK() function. I see that the function syntax is:

YEARWEEK(`dateCol`, mode)

where 'mode' defines the starting day of the week with '11' meaning Sun-Sat and '22' being Mon-Sun. I found this in the Beast Mode Functions Reference Guide but it doesn't expand whether other day ranges can be used and what their codes would be.

I would like to use Sat-Fri as my week but I can't find what mode code that would apply to, if at all?

Thanks for any help!

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Sadly this seems to be one of the functions that is not homologous to its MySQL counterpart, so unless someone has tried it, I don't think we'll have much extra documentation (You can always test and share your findings here, from my initial test any other number results in the same behavior as 11).

    image.png

    You could hack your way around this by using a DATE_ADD or DATE_SUB to move your actual date a couple of days so that the displaced calendar would align with weeks Sat-Fri, but this becomes tricky when you approach the beginning or end of the year. 

    YEARWEEK(DATE_ADD(`dt`,INTERVAL 1 DAY),11)

    Will give you weeks starting on Saturday, but as explained if might work weirdly towards the start or end of a year. In here you can see that it considers 12/31/2022 as Week 202301.

    image.png

Answers

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Sadly this seems to be one of the functions that is not homologous to its MySQL counterpart, so unless someone has tried it, I don't think we'll have much extra documentation (You can always test and share your findings here, from my initial test any other number results in the same behavior as 11).

    image.png

    You could hack your way around this by using a DATE_ADD or DATE_SUB to move your actual date a couple of days so that the displaced calendar would align with weeks Sat-Fri, but this becomes tricky when you approach the beginning or end of the year. 

    YEARWEEK(DATE_ADD(`dt`,INTERVAL 1 DAY),11)

    Will give you weeks starting on Saturday, but as explained if might work weirdly towards the start or end of a year. In here you can see that it considers 12/31/2022 as Week 202301.

    image.png

  • bgweis
    bgweis Member

    Thanks so much for this! I was seeing similar behavior regardless of the code I entered for the mode value. I was hoping I was missing something obvious - oh well. I will check with the team and see whether a consistent Sunday-Saturday week definition is okay or whether we should look at your workaround.

    Thanks again!

  • Engineer_0
    Engineer_0 Member

    I've also had issues the YEARWEEK() function not working as it should in BeastModes. However, it is worth noting that the same function (name, anyway) works perfectly, as expected in MagicETL. I've opened a support ticket (case: 05895315) about this. MagicETL leverages native MySQL, which is why YEARWEEK() works as it should in ETLs. The version available in BeastMode is something DOMO engineers stubbed out for us, but it has bugs and should be updated to use or match the native MySQL YEARWEEK() functionality.

Categories