Derived AutoCalendar Fields and Set Analysis Expressions

[smithc2005]

Good Afternoon ! 

This one is killing me. I am trying to subtract months from a derived Year Date field to use it as a set expression in my set analysis. Am I getting the format wrong? It's returning zero if I don't explicitly use my date variable set in the load script.

This is the part of the expression in question.

Variable is initially set this way;

LET vEndDate = Date(Today()-1,'YYYY-MMM');

Works :

[DATE1.autoCalendar.YearMonth]={"$(vEndDate)"} 

Does not work:

[DATE1.autoCalendar.YearMonth]={"$(=Date(AddMonths(Date#($(vEndDate),'YYYY-MMM'),-1),'YYYY-MMM'))"}

Am I not thinking through this correctly, below?

[Lisa_P]

Remembering that the Date function only changes the presentation and the YearMonth is stored internally as a number. So format is not the issue.

MonthStart(AddMonths(Date(Today()-1, 'YYYY-MMM'),-1)) will give you the correct date that should match your set analysis because you need the 1st of the Month.

My recommendation is :

[DATE1.autoCalendar.YearMonth]={"$(=MonthStart(AddMonths($(vEndDate),-1))"}

[smithc2005]

Thanks for replying!

I didn't realize I needed the first of the month to match what the autoCalendar is doing. I'm initializing my variable like this in the data load editor.

LET vEndDate = Date(MonthStart(Date(Today()-1, 'YYYY-MMM')),'YYYY-MMM');

I did it this way because I'm guessing I need the YYYY-MMM format to match how autoCalendar makes it. 

Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

$(vEndDate) on it's own in the set analysis and expands out to "2020-Sept" at the preview on the bottom.

[SALES_POSTED_DATE.autoCalendar.YearMonth]={"$(vEndDate)"}

I've used your recommendation but all I get is the hypen. Are we able to modify these derived fields using expressions?

[SALES_POSTED_DATE.autoCalendar.YearMonth]={"$(=MonthStart(AddMonths($(vEndDate),-1)))"}

I'm using this to create the autoCalendar.

[smithc2005 · Accepted solution]

Found it!

Removing the dollar sign expansion on the variable worked I'm not sure why, though. Does anyone know why ?

[SALES_POSTED_DATE.autoCalendar.YearMonth]={"$(=Date(MonthStart(AddMonths(vEndDate,-1)),'YYYY-MMM'))"}