topic Re: Calendar Dates Script - Next Month in QlikView

Calendar Dates Script - Next Month

stuwannop — Fri, 21 Mar 2014 11:43:29 GMT

Hi Everyone

I currently have a line in my script that allows me to9 flag all dates last calendar month. This is:

IF(InMonthToDate(QualifiedDate, today(), -1),1,0) where qualifiedDate is of course my date value.

I want to be able to write a line that will flag all dates in the NEXT calendar month (bear in mind if it's December 2014 it needs to go to January 2015). Can someone help me out?

Thanks in advance

Stu

Re: Calendar Dates Script - Next Month

Fri, 21 Mar 2014 11:48:13 GMT

Hi,

I'd do it this way:

-(addmonths(monthstart(QualifiedDate),-1)=monthstart(Today()))

Hope this helps

Re: Calendar Dates Script - Next Month

MK_QSL — Fri, 21 Mar 2014 11:54:36 GMT

Don't Exactly understood what you want...

If it is like you want to find last date for any date

add below in script...

MonthEnd(QualifiedDate) as MonthEndQualifiedDate

if your want last end date for next month

=MonthEnd(AddMonths(QualifiedDate,1)) as MonthEndQualifiedDateNextMonth

Re: Re: Calendar Dates Script - Next Month

stuwannop — Fri, 21 Mar 2014 12:22:05 GMT

Hi Pierre

Thanks for the quick response, that seems to return a 0 if put in an IF statement.

I tried playing around with the operators (which I think it must be to do with) but I haven't quite cracked it. I ended up with

IF(-(addmonths(monthstart(QualifiedDate),1)=monthstart(Today())),1,0) but that returns last month so I'm still trying.

Manesh - I don't want to ultimately return a date as such, I want to return a 1 or 0 depending on whether the given date (Qualified Date) falls in the next calendar month or not.

Thanks

Stu

Re: Calendar Dates Script - Next Month

Fri, 21 Mar 2014 12:25:45 GMT

It's a boolean formula, can be put in the script without an IF.

With an if, this should work:

if(addmonths(monthstart(QualifiedDate),-1)=monthstart(Today()),1,0)

Re: Calendar Dates Script - Next Month

stuwannop — Fri, 21 Mar 2014 14:12:38 GMT

Hi Pierre - Scratch my last response it actually works. User error I'm afraid - I've found a glitch in my data load. The script wasn't loading anything past todays date so of course it only returned a 0 flag.

Thanks for the answer and sorry to take up more of your time - appreciate your patience.

Stu