topic Re: Addmonths function can't work in QlikView

Addmonths function can't work

Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I create a variable (New Time) which have value 2016-9-30 and put it in addmonths function but it failed.

However when i replace (New Time) with '2016-9-30', it work..

Case 1

Addmonths('2016-9-30', 1) = 30/10/2016 it work

Case 2

Addmonths([New Time], 1) = - it failed which [New Time]=2016-9-30

Please help T^T

Re: Addmonths function can't work

Anil_Babu_Samineni — Wed, 01 Feb 2017 09:44:56 GMT

Case 1

Addmonths('2016-9-30', 1) = 30/10/2016 it work

It should work because this is a static

Case 2: Try like below

Addmonths(Date([New Time],'DD/MM/YYYY'), 1)

Update: If variable, you may create something like this

AddMonths('$([New Time])',1) //Can you confirm how do you create New Line to get that Date

Re: Addmonths function can't work

Anonymous — Wed, 01 Feb 2017 09:51:49 GMT

is [New Time] a variable or a field?

Are you using addmonths in script or in a chart?

Re: Addmonths function can't work

Anonymous — Wed, 01 Feb 2017 09:55:28 GMT

if its a variable:

=AddMonths('$(New Time)',1)

Re: Addmonths function can't work

jonathandienst — Wed, 01 Feb 2017 09:58:50 GMT

You need quotes around the variable:

Addmonths('[New Time]', 1)

Re: Addmonths function can't work

Thu, 02 Feb 2017 01:20:11 GMT

It doesn't work...

New Time should be a field

I create New Time in script:

	year,
	month,
	Timestamp#(year &'-'& month &'-30','YYYY-MM') as "New Time",

year and month is the original field

Re: Addmonths function can't work

Thu, 02 Feb 2017 07:49:56 GMT

New Time should be a field

I create New Time in script:

	year,
	month,
	Timestamp#(year &'-'& month &'-30','YYYY-MM') as "New Time",

year and month is the original field

I have tried =AddMonths('$(New Time)',1) and it doesn't work...

Re: Addmonths function can't work

Anonymous — Mon, 06 Feb 2017 09:55:51 GMT

Hi,

This should work:

DateTest:
LOAD
year,
month,
Monthend(Date#(year &'-'& month &'-01','YYYY-MM-DD')) as [New Time],
addmonths(Monthend(Date#(year &'-'& month &'-01','YYYY-MM-DD')),1) as [New Time Addmonths];
LOAD * INLINE [
year, month
2016, 01
2016, 02
2016, 03
2016, 04
2016, 05
2016, 06
];

Just modify and apply to your scenario.

- Use monthend function instead to determine last date of a month - Not all months have 30 days

Hope this helps!

Re: Addmonths function can't work

sdmech81 — Mon, 06 Feb 2017 10:27:24 GMT

HI,

Use makedate function and generate date as u desire

U have used like :Timestamp#(year &'-'& month &'-30','YYYY-MM') as "New Time"

And Timestamp# is interpretation-function not date generation function.

Use makedate() and then try Addmonth() it should work!

Sachin

Re: Addmonths function can't work

sdmech81 — Mon, 06 Feb 2017 10:29:45 GMT

Examples fr make date:

MakeDate(YYYY [ , MM [ , DD ] ] )

Returns a date calculated from the year YYYY, the month MM and the day DD.

If no month is stated, 1(January) is assumed.

If no day is stated, 1 (the 1:st) is assumed.

Examples:

makedate(1999) returns 1999-01-01

makedate(99) returns 0099-01-01

makedate(1992,12) returns 1992-12-01

makedate(1999,2,14) returns 1999-02-14