topic Re: Calendar Script (This month a year ago) in QlikView

Calendar Script (This month a year ago)

stuwannop — Mon, 05 Jan 2015 17:06:48 GMT

Hi All

I've had a search through various threads but can't quite find what I want. I have a calendar script set up to return 1 or 0 for various date conditions but I'm missing a couple and I hope someone can help.

So far I have:

if(InYearToDate (Date, today(),0,4),1,0)									as Dates_FYTD_TY (Returns 1 for all dates in this financial year to date (starting in April)
if(InYearToDate (Date, today(),-1,4),1,0)									as Dates_FYTD_LY,(Returns 1 for all dates in previous financial year to date
if(InMonthToDate(Date, today(), -1),1,0)									All dates last month

I'm trying to now return a 1 or 0 for:

Any dates that are the previous year of the last month (in context of this that would be December 2013.

Thanks in advance

Stu

Re: Calendar Script (This month a year ago)

rwunderlich — Mon, 05 Jan 2015 18:00:09 GMT

I think it would be InMonthToDate uisng an offset of -13. You may have to adjust that number for your fiscal year.

=InMonthToDate(makeDate(2013,12,1),today(),-13)

-Rob

http://masterssummit.com

http://robwunderlich.com

Re: Calendar Script (This month a year ago)

Mon, 05 Jan 2015 19:09:08 GMT

Just an FYI - using InMonthToDate would give you only till today.

if(InMonthToDate(Date, today(), -1),1,0) - Dates till today of last Month (If today is 1/5/2015 then you would get 12/1/2014 to 12/5/2014)

Re: Calendar Script (This month a year ago)

rwunderlich — Mon, 05 Jan 2015 19:24:42 GMT

Good point. To get the full previous month, insert MonthEnd() like this:

=InMonthToDate(makeDate(2013,12,31),MonthEnd(today(1)),-13)

-Rob

Re: Calendar Script (This month a year ago)

stuwannop — Tue, 06 Jan 2015 09:36:35 GMT

Thank you both for your responses.

I couldn't quite get it to work using IF(InMonthToDate(makeDate(2013,12,31),MonthEnd(today(1)),-13),1,0) it just returned a 1 all the time - it's probably me not understanding the funtion correctly to be fair. (I also don't want to hard code December 2013 either sorry if I didn't make that clear)

I think the key is the 13 months though as I find the following solution seems to now work:

if(InMonthToDate(Date, today(), -13),1,0)

Re: Calendar Script (This month a year ago)

rwunderlich — Tue, 06 Jan 2015 16:36:47 GMT

December 2013 was just an example. You should use your own Date field as you apparently figured out.

-Rob