Whats wrong with my set-expression?

[Bobi]

Hi. I have tried to create a canonical date for the case where I have one datefield (d_enddate) and one datefield (d_startdate) in the same table. I created a bridge table like this:

DateBridge:

LOAD [c_caseref], Dual(Year([c_startdate])&'-'&Month([c_startdate]), monthstart([c_startdate])) AS CanonicalDate, 'Start' as DateType

RESIDENT [t_case_h];

LOAD [c_caseref], Dual(Year([c_enddate])&'-'&Month([c_enddate]), monthstart([c_enddate])) AS CanonicalDate, 'End' as DateType

RESIDENT [t_case_h];

In the table I have 2 set-expression based on 2 different variables. This all works perfectly fine for the first set expression: 

= count({$<t_diary.c_name={'Tillsynsärenden'}, [c_startdate.autoCalendar.YearMonth]={"$(vLMaxYearMonth)"}>} distinct c_caseref)

variable:

=date(max([c_startdate.autoCalendar.YearMonth]),'YYYY-MMM')

This doesnt work however, 

set expression 2:

= count({$<t_diary.c_name={'Tillsynsärenden'}, [c_startdate.autoCalendar.YearMonth]={"$(vLPMaxYearMonth)"}>} distinct c_caseref)

variable: vLPMaxYearMonth=date(addmonths(max([c_startdate.autoCalendar.YearMonth]),-1),'YYYY-MMM')

For the 2nd set-expression I get 0 when making a selection. The only difference between the two expressions is the variable. And the variable worked fine before making the canonical date. 

Anyone having a suggestion how to correct this?

[lfetensini]

Apparently the function of adding months is not being interpreted correctly. Because it will be tricky to know what happens. Otherwise you can be able to force the formula this way:

Old Variable: vLPMaxYearMonth
=date(addmonths(max([c_startdate.autoCalendar.YearMonth]),-1),'YYYY-MMM')

New Variable: vLPMaxYearMonth
=Date(AddMonths(Date(Max([c_startdate.autoCalendar.YearMonth])),-1),'YYYY-MMM')

"Date before" AddMonths to force a Date Property. And the First Date to Format in 'YYYY-MMM'.