Custom Calendar Week On Dataset?

pauljames
pauljames Contributor
in Magic ETL

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!

IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

Tagged:

Best Answer

  • rco
    rco Domo Employee
    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>

Answers

  • GrantSmith
    GrantSmith Coach

    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!**
  • pauljames
    pauljames Contributor

    @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!

  • rco
    rco Domo Employee
    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>

Categories