Last Year MTD show 0 when year filter selected.

[louisdch]

Hi all,

I have applied below expression for Last Year MTD.

It works fine when no filter is applied or Month / Date filter is applied.

But when i select Year filter, it only show 0.

Sum({<[SALES.SALES_DATE.autoCalendar.fYear2] =,[SALES.SALES_DATE.autoCalendar.Month]=,SALES.SALES_DATE = ,[SALES.SALES_DATE.autoCalendar.DateNum]={">=$(=Num(AddYears(MonthStart(max([SALES.SALES_DATE.autoCalendar.DateNum])),-1))) <=$(=Num(AddYears(max([SALES.SALES_DATE.autoCalendar.DateNum]),-1)))"} >} [SALES.SALES_AMOUNT])

Any idea?

Best Regards,

Louis

[arvind_patil]

Hi Louis,

Please check name of filed you are selecting and simply nullify this from set analysis.

Thanks,

Arvind Patil

[kenji_khoo]

Use island calendar to control the selection. and apply set analysis with variables.

sum({<Date={$(vDateMTD)}>}Sales)

vMaxDate :

=floor(num(max(Date)))

vDateMTD :

='>='& floor(num(monthstart(vMaxDate))) &'<='& vMaxDate

for last year, you can do

vLYDateMTD :

='>='& floor(num(monthstart(addmonths(vMaxDate,-12)))) &'<='& addmonths(vMaxDate,-12)