Custom Calendar Week On Dataset?
Best Answer
-
Ideally you'd figure out how to express your week system in terms of one of Magic ETL's built in week modes. Then you can use the WEEK and YEARWEEK functions plus your adjustment to arrive at the year and week you want for any given date.
The WEEK and YEARWEEK functions both accept an optional second argument to indicate their mode. The modes behave as follows:
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.
For example, if your week begins on Sunday and your definition of "third week of January" means "third week with a Sunday in January", then you might use week mode 2 with an offset of negative 14 days, like so:
WEEK(my_date_column - interval 2 week, 2)
I suggest trying out some combinations of week modes and offsets to see if any match your rules. If none do, then we may need to come up with a solution involving a calendar dataset to be joined in to assist with the calculation.
Randall Oveson <randall.oveson@domo.com>
0
Answers
-
you could create your own Date dimension dataset in an ETL and supply your own logic. Then you can ties that y to your datasets to get your custom calendar information
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith how would I write a case statement to categorize 2024-03-30 to 2024-04-05 as CW10?
IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!
0 -
Ideally you'd figure out how to express your week system in terms of one of Magic ETL's built in week modes. Then you can use the WEEK and YEARWEEK functions plus your adjustment to arrive at the year and week you want for any given date.
The WEEK and YEARWEEK functions both accept an optional second argument to indicate their mode. The modes behave as follows:
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.
For example, if your week begins on Sunday and your definition of "third week of January" means "third week with a Sunday in January", then you might use week mode 2 with an offset of negative 14 days, like so:
WEEK(my_date_column - interval 2 week, 2)
I suggest trying out some combinations of week modes and offsets to see if any match your rules. If none do, then we may need to come up with a solution involving a calendar dataset to be joined in to assist with the calculation.
Randall Oveson <randall.oveson@domo.com>
0
Categories
- All Categories
- 1.5K Product Ideas
- 1.5K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 278 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 89 SQL DataFlows
- 560 Datasets
- 2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 575 Beast Mode
- 13 App Studio
- 28 Variables
- 582 Automate
- 141 Apps
- 414 APIs & Domo Developer
- 26 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 356 Distribute
- 95 Domo Everywhere
- 259 Scheduled Reports
- 2 Software Integrations
- 92 Manage
- 89 Governance & Security
- 9 Product Release Questions
- Community Forums
- 42 Getting Started
- 28 Community Member Introductions
- 89 Community Announcements
- 4.8K Archive