YEARWEEK Function and Modes
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
-
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.
1
Answers
-
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.
1 -
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!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive