Issue in expression- last year MTD

[Anonymous]

Hi Friends

I am using below expression to get last year MTD.

Example , if today is 10th, then we should get Oct 1st 2017 to 10th 2017. However using below expression is giving me 0.

sum( {<Year=,Month=,Date={">=$(=AddYears(MonthStart(max(Date)),-1)) <=$(=AddYears(max(Date),-1))"} >}   SALES)

Kindly help to resolve the issue in above expression.

Thanks & Regards

Chintan Gala

[Anonymous]

Give an example of your date format please

[Anil_Babu_Samineni]

You can simplify this?

Sum({<DateField = {">=MonthStart(Max(DateField))<=$(=MonthEnd(Max(DateField)))"}>} SALES)

[jonathandienst]

Assuming you are making selections in Year and Month (and not in other date fields like Quarter) and that there is data for last year, then the problem might be that Date is not in the default date format for your system.. Then you may get success with

sum({<Year, Month, Date = {">=Date($(=AddYears(MonthStart(max(Date)),-1)), '<date format here>') <=Date($(=AddYears(max(Date),-1)), '<date format here>')"}>} SALES)

Enter the date format for Date into the format strings above to align the date formats.

[MindaugasBacius]

Try this:

=sum({<Date = {">=$(=Date(AddYears(MonthStart(Today()), -1), 'YYYY.MM.DD'))<=$(=Date(AddYears(Today(), -1), 'YYYY.MM.DD'))"}>}SALES)

[Anonymous]

Dear Anil

I believe this will not give last year MTD. We are trying to achieve for Oct 1 to 10 , 2018.

Thanks.

[Anil_Babu_Samineni]

Not sure, Why last year MTD you need? But, Try this to get last Year date from Oct 1st 2017 - Oct 31st 2017 ??

Sum({<DateField = {">=MonthStart(AddYears(Today(),-1))<=$(=MonthEnd(AddYears(Today(),-1)))"}>} SALES)

[Anonymous]

Still getting 0 with this. I replaced the date format with DD-MM-YYYY

[MindaugasBacius]

Try to debug and split to smaller steps:

Create text objects for:

1. =Date(AddYears(MonthStart(Today()), -1), 'YYYY.MM.DD')

2. =Date(AddYears(Today(), -1), 'YYYY.MM.DD')

Do you get what you are expecting?

[Anonymous]

Yes in text objects we get , Oct 1 2017 and Oct 10 , 2017 correctly. But when we are using  in expression it gives 0 .