InMonthsToDate() problems

Mon, 09 Nov 2009 09:08:44 GMT

I have a temp table that gets autofilled with dates (1/1/2009 - Today())

Iwould like to flag all dates that are within the previous 6 months.

Using InMonthsToDate() yields strange results.

InMonthsToDate(3,myDate, Today(),0) flags dates for the past 2 months

InMonthsToDate(4,myDate, Today(),0) flags dates for the past 3 months

I'm not sure why its not going back 3 and 4 months respetively....odd behavior

Similaraly

InMonthsToDate(6,myDate, Today(),0) flags dates for the past 5 months (7/1/2009 - 11/8/2009)

I can't put a 7 in the first parameter, it isn't allowed.

So how do I flag these dates?

Thanks

-Andy

Bill_Britt — Mon, 09 Nov 2009 09:25:12 GMT

You might try something like this.

inmonthtodate

(D, $(vToday), 0) * -1 AS CMTD, //Current month-to-date

inmonthtodate(D, $(vToday), -1) * -1 AS PMTD, //Previous month-to-date

inmonthtodate(D, $(vToday), -12) * -1 AS FPMTD, //First prior month-to-date, same month last year

inmonthtodate(D, $(vToday), -24) * -1 AS SPMTD

//Second prior month-to-date, same month two years ago

InMonths ( n, date, basedate , shift [, first_month_of_year = 1] )

topic InMonthsToDate() problems in QlikView