Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

addmonths() not working in my set analysis

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

5 Replies
JonnyPoole
Employee
Employee

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?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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.