Custom Calendar Week On Dataset?

pauljames

Hey! What if I want to establish a new calendar on a dataset?

For instance, the third week of January would be CW1 and each week starts on a Saturday.

thanks!

rco

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.

GrantSmith

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

pauljames

@GrantSmith how would I write a case statement to categorize 2024-03-30 to 2024-04-05 as CW10?

rco

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.