Skip to content
AstratoAstratoAstrato

How do I create a custom flag field in Astrato?

Updated 23 Oct 2026

CASE     WHEN DAYOFWEEK("YOURDATEFIELD") BETWEEN 2 AND 6          AND "ORDER_DATE_NEW" = CASE                                      DAYOFWEEK(DATE_TRUNC('MONTH', "YOURDATEFIELD"))                                      WHEN 1 THEN DATEADD('day', 1, DATE_TRUNC('MONTH', "YOURDATEFIELD")) -- If 1st is Sunday                                     WHEN 7 THEN DATEADD('day', 2, DATE_TRUNC('MONTH', "YOURDATEFIELD")) -- If 1st is Saturday                                     ELSE DATE_TRUNC('MONTH', "YOURDATEFIELD") -- If 1st is weekday                                 END     THEN 'Y'    WHEN DAYOFWEEK("YOURDATEFIELD") BETWEEN 2 AND 6     THEN 'N'END
  • When you replace YOURDATEFIELD – please make sure you are using the table’s Source name and not the Field name

Understanding the above code:

  1. It only looks at weekdays (Monday through Friday) and ignores weekends completely.

  2. It figures out the first weekday of each month - if the 1st falls on a weekend, it moves to the following Monday.

  3. It returns ‘Y’ if the date is that first weekday of the month, otherwise it returns ‘N’ for all other weekdays.

  4. The result is a flag that marks only the first business day of each month - useful for identifying month-start dates in business reporting.