Creating a New column

ColinHaze

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.

MichelleH

@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

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.

MichelleH

@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

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.

GrantSmith

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

ColinHaze

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?