topic Re: Function help in QlikView

Function help

hari8088 — Sat, 01 Aug 2015 07:46:25 GMT

Hi,

Addmonths('10/05/2009',3)

if i use this function it is adding 3 month to that using date it is giving 10/08/2009.

but when i use three parameters it is giving date differently.

for example :addmonths('10/05/2009',3,1) here how the second paramater is working onthe date?

Re: Function help

antoniotiman — Sat, 01 Aug 2015 07:55:58 GMT

If Date is last Month Day e.g. 30/09/2010

AddMonths('30/09/2009',1) -> 30/10/2010

AddMonths('30/09/2009',1,1) -> 31/10/2010

Re: Function help

jagan — Sat, 01 Aug 2015 08:14:11 GMT

Hi Hari,

From Qlikview help file

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'

If you specify 1 as 3rd parameter it will give the month end date after adding the given months.

Regards,

Jagan.

Re: Function help

hari8088 — Sat, 01 Aug 2015 08:24:42 GMT

Hi Jagan,

I understand but my doubt is

from qlikview help the bellow date how it is giving 26 as output date.

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

Re: Function help

jagan — Sat, 01 Aug 2015 09:49:26 GMT

Not sure may be may be bug, hic‌ can help on this.

Regards,

Jagan.

Re: Function help

maxgro — Sat, 01 Aug 2015 10:09:36 GMT

I think with mode=1

29 is 2 days before the end of January

26 is 2 days before the end of February

calculated day as derived from the end of the month (mode=1)

Re: Function help

qlikviewwizard — Sun, 02 Aug 2015 06:23:21 GMT

hari8088 What is your date format?

Please upload your sample file.

Thank you.

Re: Function help

awhitfield — Mon, 03 Aug 2015 13:45:10 GMT

Hi,

the 26 is correct as there were only 28 days in Feb 2003

Andy

Re: Function help

kavita25 — Wed, 05 Aug 2015 09:42:41 GMT

Hi,

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

as 2003-01-29 is 2 days before of the end date (January has 31 days), therefore the result for the month of April is 2 days before(April has 30 days) 2003-04-28

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

same here January has 31 days and February has 28 days, therefore the result is 2 days before of February month

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

2003-02-28 is the last day for the month of February, therefore with the add of 1 month i.e March its showing the last date...

Hope it helps you!!!

Regards,

Kavita