6 Replies Latest reply: Aug 10, 2017 3:54 PM by Mona Lk

# Understanding the InMonths function

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';
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

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

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

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

Hello !

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

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