Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smithc2005
Contributor III
Contributor III

Derived AutoCalendar Fields and Set Analysis Expressions

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?

Screenshot 2020-09-23 151042.png

 

Labels (2)
1 Solution

Accepted Solutions
smithc2005
Contributor III
Contributor III
Author

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'))"}

View solution in original post

3 Replies
Lisa_P
Employee
Employee

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
Contributor III
Contributor III
Author

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)"}

Screenshot 2020-09-23 224615.png

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)))"}

Screenshot 2020-09-23 2248501.png

 

 

I'm using this to create the autoCalendar.

smithc2005
Contributor III
Contributor III
Author

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'))"}