Creating a New column

ColinHaze
ColinHaze Member
in Magic ETL

Hi,

I am trying to figure out the easiest way to create a new column. The new column would be taking dates and assigning them to a month. For example the values assigned to NOV 2023 would be between Oct 27th(3 working days (excluding weekends) of the last month to Nov 27th (3 working days before the last day of the month. The values assigned to DEC would be Nov 28th to Dec 26th and so forth. I see you can add a date operation or new constraint but it doesn't give an option to select dates that are between certain dates. Any advice would be much appreciated.

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @ColinHaze There are a couple different options you could use:

    1. You can reach out to your CSM about enabling a fiscal calendar in your instance. Here is a knowledge base article about how they work: https://domo-support.domo.com/s/article/360042924254?language=en_US
    2. You can upload a file to Domo with a row for every date and its corresponding month. Then you can join your data to that mapping table on date in MagicETL
  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    I would suggest taking a look at the calendar dataset in the Domo Dimensions connector. You can use this calendar to dynamically get the last working day of each month. Then, you could use the formula tile to assign the month groupings based on a comparison of the calendar data and your data.

    If I solved your problem, please select "yes" above

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @ColinHaze There are a couple different options you could use:

    1. You can reach out to your CSM about enabling a fiscal calendar in your instance. Here is a knowledge base article about how they work: https://domo-support.domo.com/s/article/360042924254?language=en_US
    2. You can upload a file to Domo with a row for every date and its corresponding month. Then you can join your data to that mapping table on date in MagicETL
  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    I would suggest taking a look at the calendar dataset in the Domo Dimensions connector. You can use this calendar to dynamically get the last working day of each month. Then, you could use the formula tile to assign the month groupings based on a comparison of the calendar data and your data.

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach

    To clarify you want to have a specific month be the last 3 working days of the last month until the last 3 working days of the specific month?

    You have a couple of options:

    1 - Create a custom date dimension where you assign each date to a specific month

    2 - Use Magic ETL and a formula tile to calculate what month each date is a part of

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ColinHaze
    ColinHaze Member

    GrantSmith Yes exactly , I am trying to use option 2 you mentioned. In the formula tile I assume I would use a case statement and then use between to get the dates then assign them to a month, correct?

Categories