Addmonths function with a negative integer

[ieuyar]

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?

[sunny_talwar]

May be this:

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

[ieuyar]

Unfortunately, it did not work.

[vinieme12]

try

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

[sunny_talwar]

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?

[Anonymous]

Check if field "FiscalDate" is in date format.

[ieuyar]

This is result according to my script:

And here is according to yours:

[ieuyar]

It did not work.

It works this way: ,

[vinieme12]

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

[sunny_talwar · Accepted solution]

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;