Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi
Did you check the date format in both are same?
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.
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
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.