topic Re: Addmonths function with a negative integer in QlikView

Addmonths function with a negative integer

ieuyar — Mon, 23 Jan 2017 12:40:18 GMT

Hi all,

I'm trying to change the start date of periods from 1st day of month to 26th day of previous month.

For example, For December 2016 Period, it should be between November 26, 2016 and December 25, 2017

When I'm trying to load below, qlikview does not subtract a month but adds it.

Date(AddMonths(MonthStart(FiscalDate+25),-1), 'YYYY-MM') as Period

Could you please help me to solve this?

Re: Addmonths function with a negative integer

sunny_talwar — Mon, 23 Jan 2017 12:47:57 GMT

May be this:

Date(AddMonths(MonthStart(FiscalDate),-1)+25, 'YYYY-MM') as Period

Re: Addmonths function with a negative integer

ieuyar — Mon, 23 Jan 2017 12:57:35 GMT

Unfortunately, it did not work.

Re: Addmonths function with a negative integer

vinieme12 — Mon, 23 Jan 2017 13:28:32 GMT

try

Date(MonthStart(FiscalDate,-1)+25,'YYYY-MM') as Period

Re: Addmonths function with a negative integer

sunny_talwar — Mon, 23 Jan 2017 13:28:49 GMT

Can you share few rows of data to show what you are looking to do? what was the issue when you used your own script? What was different when you used mine?

Re: Addmonths function with a negative integer

Anonymous — Mon, 23 Jan 2017 13:39:56 GMT

Check if field "FiscalDate" is in date format.

Re: Addmonths function with a negative integer

ieuyar — Mon, 23 Jan 2017 14:27:07 GMT

This is result according to my script:

And here is according to yours:

Re: Addmonths function with a negative integer

ieuyar — Mon, 23 Jan 2017 14:30:23 GMT

It did not work.

It works this way: ,

Re: Addmonths function with a negative integer

vinieme12 — Mon, 23 Jan 2017 15:47:06 GMT

what exactly do you want to show in period? you wanted to subtract a month , the expression is subtracting a month!!

Re: Addmonths function with a negative integer

sunny_talwar — Mon, 23 Jan 2017 15:54:26 GMT

Try this:

Table:

LOAD Date(AddMonths(FiscalDate, -1) + 25) as Date,

Date(FiscalDate, 'YYYY-MM') as Period;

LOAD Date(MakeDate(2015, 12, 31) + IterNo()) as FiscalDate

AutoGenerate 1

While IterNo() <= 366;