YEARWEEK Function and Modes

bgweis · 2023-06-12T20:14:44+00:00

There was an error rendering this rich post.

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!

Find more posts tagged with

Accepted answers

marcel_luthi

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

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.

All comments

marcel_luthi

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.

bgweis

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

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.