addmonths() not working in my set analysis

[#1]

Hi,

I am trying to get previous month sales, but seems addmonths() is not working in my expression, it always return the current month's sales, can anyone help debug?

=sum({$<Period_ID = {"=$(=MonthEnd(AddMonths(max(Period_ID),-1)))"}>} Sales)

my Period_ID is the month end date in my date

Thanks,

XX

[JonnyPoole]

MonthEnd returns a timestamp and i'm not sure what Period_ID is... date or timestamp. You could try wrapping a floor() function around the monthend() function

=sum({$<Period_ID = {"=$(=floor(MonthEnd(AddMonths(max(Period_ID),-1))))"}>} Sales)

...but dates and set analysis can be finicky and a sample would help.

[MayilVahanan]

Hi

Did you check the date format in both are same?

[jagan]

Hi,

Try this

=sum({$<Period_ID = {"$(=MonthEnd(max(Period_ID),-1))"}>} Sales)

If not working the Period_ID  and Today() are in the same format?  If possible attach sample file.

Regards,

Jagan.

[#5]

Hi All,

Thanks for your reply, I have tried your expression and the result returned is 0 now.

my Period_ID is loaded in date format 'DD/MM/YYYY', date(Period_ID, 'DD/MM/YYYY') as Period_ID

=today ()   ---> 18/09/2014

=max(Period_ID) --> 31/08/2014

=monthend(addmonths(max(Period_ID),-1)) --> 31/07/2014

=date($(=monthend(addmonths(max(Period_ID),-1)))) -->30/12/1899

I thought the last expression should give me the same result as the 3rd one, but something is wrong with it..

Regards,

XX

[jagan]

Hi,

Tried this expression

=sum({$<Period_ID = {"$(=MonthEnd(max(Period_ID),-1))"}>} Sales)

You have sales for this date 31/07/2014?  check it.  If possible attach some sample file.

Regards,

Jagan.