topic Re: Understanding the InMonths function in QlikView

Understanding the InMonths function

Thu, 15 Nov 2012 10:06:59 GMT

I'm having great difficulties understanding the InMonths function. I have read the documentation several times and created simple tests, but I cannot find the logic.I was thinking I could use it to check if a date lies inside a range prior to the current month, ie. now when it's November I'd like to mark prior 3 months including August, September, October. In December I'd like to mark September, October and November etc.

I could use the following:

InMonth(MyDate, Now(), -3) or InMonth(MyDate, Now(), -2) or InMonth(MyDate, Now(), -1)

but IMO that's a very ugly solution and not very reusable when I need to include, say, last 15 months.

Here's my unsuccessful test:

LOAD
          *,
          If(InMonths(3, MyDate, Now(), -1) , 1, 0) AS 'Prior 3 months',
          If(InMonth(MyDate, Now(), -3) or InMonth(MyDate, Now(), -2) or InMonth(MyDate, Now(), -1), 1, 0) AS 'Wanted result';
LOAD 
          Date(MyDate, 'YYYY-MM-DD') AS MyDate,
          Test
 INLINE
          [MyDate, Test
          2012-01-01, One
          2012-02-01, Two
          2012-06-01, A
          2012-06-30, B
          2012-07-01, C
          2012-07-31, D
          2012-08-01, E
          2012-08-31, F
          2012-09-01, G
          2012-09-30, H
          2012-10-01, I
          2012-10-31, J
          2012-11-01, K
          2012-11-14, L
          2012-11-15, M
          2012-11-16, N
          2012-11-31, O
          2012-12-01, P];

I relied on this function because it worked fine last month. Now it doesn't. Could someone please explain me the logic of InMonths?

Re: Understanding the InMonths function

swuehl — Thu, 15 Nov 2012 10:54:19 GMT

I think it could look like

If(InMonths(3, MyDate, Now(), -1,month(now())) , 1, 0) AS 'Prior 3 months',

Re: Understanding the InMonths function

Thu, 15 Nov 2012 11:35:17 GMT

That seems to work, but I'd like to understand why.

Why does the documentation specify that N (the 1st parameter) must be (1), 2, (3), 4 or 6, and why are 1 and 3 in parentheses? Why is that a must? If it defines the range size in months, why can't I set N to any arbitrary number of months?

What is the purpose of basedate, if the first_month_of_year has to be defined as well? What has January 1st to do with this function anyway? The definition of this function is very unclear.

Re: Understanding the InMonths function

swuehl — Thu, 15 Nov 2012 11:47:07 GMT

I am rarely using InMonths() function, if ever.

I assume it's just a more generalized function that may be used instead of InMonth() (if used 1 as first argument) and InQuarter() (if used 3 as first argument).

Instead of above expression, you can also use:

if(MyDate >= MonthStart(now(),-3) and MyDate <=monthend(now(),-1),1,0) as 'Prior 3 months',

which I found much easier to read and understand. And even much more flexible to adapt to e.g. periods like last 15 months.

Regards,

Stefan

Re: Understanding the InMonths function

Thu, 10 Aug 2017 19:54:41 GMT

Hello !

And for this expression what can I use? I don't understand:

InMonths(3, MyDate, Now(), 0)

Please help me.

Re: Understanding the InMonths function

Thu, 10 Aug 2017 19:59:20 GMT

Something like this:

If(MyDate >= YearStart(Now()) and MyDate <=MonthEnd(YearStart(Now()) , 2),1,0) ?

I don't understand.