topic Re: Addmonths function in QlikView

Addmonths function

vikasmahajan — Fri, 22 Mar 2013 10:30:52 GMT

Dear All,

Can anybody explain addmonths with mode in details ?

Thanks

Vikas

Re: Addmonths function

Gysbert_Wassenaar — Fri, 22 Mar 2013 10:35:30 GMT

The addmonths creates a new date from the input date by adding or subtracting the number of months you specify as the second argument to the function.

The Help file is pretty clear imho:

AddMonths(startdate, n , [ , mode] )

Returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.

By specifying a mode (0 if omitted) the date is set to either the unmodified day of the specified month (mode=0) or the calculated day as derived from the end of the month (mode=1).

Examples:

addmonths ('2003-01-29',3) returns '2003-04-29'

addmonths ('2003-01-29',3,0) returns '2003-04-29'

addmonths ('2003-01-29',3,1) returns '2003-04-28'

addmonths ('2003-01-29',1,0) returns '2003-02-28'

addmonths ('2003-01-29',1,1) returns '2003-02-26'

addmonths ('2003-02-28',1,0) returns '2003-03-28'

addmonths ('2003-02-28',1,1) returns '2003-03-31'

Re: Addmonths function

alexandros17 — Fri, 22 Mar 2013 10:39:34 GMT

you can understand the functio with this example:

addmonths
('2003-01-29',1,0) returns '2003-02-28'

addmonths
('2003-01-29',1,1) returns '2003-02-26'

in the second case I'm Adding a month to january but the month I'm adding is february that has 28 days that summed to january 29th is february 26 ....

Hope it helps

Re: Addmonths function

Fri, 22 Mar 2013 10:42:24 GMT

Hello Vikas,

Gysbert has explained very well, I would like to explain the results :

addmonths ('2003-01-29',3) returns '2003-04-29'

--will add 3 months

addmonths ('2003-01-29',3,0) returns '2003-04-29'

--will add 3 months

addmonths ('2003-01-29',3,1) returns '2003-04-28'

--will add 3 months, BUT enddate of Jan 2003 is 2003-01-31 and input date is 2 days less. So date returned is 3 months, that is April 2003 enddate(2003-04-30), but 2 days less which will give 2003-04-28

Thanks,

angad

Re: Addmonths function

vikasmahajan — Fri, 22 Mar 2013 11:12:23 GMT

Thanks for clearing doubts

Please check my expression using Mode my problem is solved

I used following expression for last year and use 1 mode for hadling leap year

addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12,1))

Whether it is ok ?

Regards

Vikas

Re: Addmonths function

Mon, 22 Apr 2013 15:16:30 GMT

Hi guys,

Did great explanation..

Then think about this once

let

x=addmonths('2012-1-20',1,0); //2012-2-20

let y=addmonths('2012-1-20',1,1); //2012-2-20

i have checked these two dates. Result is same even mode is different .

Plz explain once .. why it is getting same.

Re: Addmonths function

Mon, 22 Apr 2013 15:16:52 GMT

Hi guys,

Did great explanation..

Then think about this once

let

x=addmonths('2012-1-20',1,0); //2012-2-20

let y=addmonths('2012-1-20',1,1); //2012-2-20

i have checked these two dates. Result is same even mode is different .

Plz explain once .. why it is getting same.

Re: Addmonths function

Mon, 22 Apr 2013 15:17:13 GMT

Hi guys,

Did great explanation..

Then think about this once

let

x=addmonths('2012-1-20',1,0); //2012-2-20

let y=addmonths('2012-1-20',1,1); //2012-2-20

i have checked these two dates. Result is same even mode is different .

Plz explain once .. why it is getting same.

Re: Addmonths function

Mon, 22 Apr 2013 15:19:52 GMT

try same example with different modes.Try examples which are not mentioned in the qlikview reference manual

for Eg:

let x=addmonths('2012-1-20',1,0)

;

let y=addmonths('2012-1-20',1,1);

Please explain , why two outputs are same????

Re: Addmonths function

Mon, 22 Apr 2013 15:23:04 GMT

try same example with different modes.Try examples which are not mentioned in the qlikview reference manual

for Eg:

let x=addmonths('2012-1-20',1,0)

;

let y=addmonths('2012-1-20',1,1);

Please explain , why two outputs are same????

Re: Addmonths function

vikasmahajan — Mon, 22 Apr 2013 15:30:12 GMT

Hello ,

I think this works only when year is leap but I am not sure

Thanks

Vikas

Re: Addmonths function

Wed, 11 Sep 2013 17:16:15 GMT

Hello Satti,

This is because the next month which you are adding is of 28 days. And any date before 28th of feb would return same date irrespective of mode.

Thanks,

ASINGH

Re: Addmonths function

michael_anthony — Thu, 12 Sep 2013 05:53:27 GMT

Mode 1 is only effective when the starting date is >= 28th of the month. Any earlier day returns same for both modes. This applies to all months, not just February. Presume this is because all months have at least 28 days so there is no confusion of how to add a month, whereas 28,29,30 and 31 could be interpreted differently.

Re: Addmonths function

Fri, 15 May 2015 14:37:17 GMT

Hi Gysbert,

I need a help from you, Please help me on this :

I have 3 months date, Suppose - 1 Jan to 31 Jan, 1 Feb to 28 Feb and for March - 1 Mar 2015. Now I am in QlikView script to load this, It is reloading . But Output should to come like

Invoice Date : 31 Jan 2015, 1 Feb 2015 to 28 Feb (Whole Date for Feb Month) and no date for 1st Mar 2015.

Re: Addmonths function

buzzy996 — Fri, 15 May 2015 16:42:57 GMT

addmonths() --help to add/remove few months to ur current selection date.

go with reference manual for more details!