Date_ADD / CONCAT MONTHNAME - Syntax Error

Hey Everyone! 

 

I am trying combine two different beast modes I am already using and keep running into syntax errors. 

 

DATE_ADD(`Submit Date`,21 day)      - Used to add +21 days to submit date for new date 

 

I am wanting to add 

 

CONCAT(MONTHNAME(`Locked`),
(CASE WHEN DAY(`Locked`) <= 15
THEN ' 1st - 15th'
ELSE ' 16th - EOM'
END)
)

 

to the same beast mode so the new dates are now broken down by 1st-15th and 15th-EOM.

 

Any help would be appreciated! 

 

Thanks!!  

Comments

  • Hi @Whimzyy 

    Try this:

    CONCAT(MONTHNAME(DATE_ADD(`Submit Date`,21 day)),
    (CASE WHEN DAY(DATE_ADD(`Submit Date`,21 day)) <= 15
    THEN ' 1st - 15th'
    ELSE ' 16th - EOM'
    END)
    )

     

     

    It's also helpful next time if you post your current beast mode you're having issues with to help get a better idea of what you're attempting to do and the exact syntax to help diagnose your error. 

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • is it beating a dead horse to say that this type of calc should be executed in a date dimension?

     

    If it were me, 

     

    1, in ETL i'd add the 'Submit Date (Date + 21 days) to the fact table.

    2 create a date dimension where for each day i indicate "isFirstHalfOfMonth" as a binary. AND add the concatenation of MonthName + 'H1' or 'H2' which IMHO is a cleaner representation of 1-15 vs 16-EOM.  

     

    then JOIN the date Dimension to the fact table on Submit Date.

     

    What will ultimately happen is people will want to change the representation of H1 and H2 OR you'll have new business rules for what happens in February.  Do you really want to have to manually update / maintain all the cards using this beast mode?

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"