Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Employee
Employee

Re: addmonths() not working in my set analysis

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.

Re: addmonths() not working in my set analysis

Hi

Did you check the date format in both are same?

MVP
MVP

Re: addmonths() not working in my set analysis

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

Re: addmonths() not working in my set analysis

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

MVP
MVP

Re: addmonths() not working in my set analysis

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.




Community Browser