Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

ieuyar
New Contributor III

Addmonths function with a negative integer

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?

Tags (1)
1 Solution

Accepted Solutions

Re: Addmonths function with a negative integer

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;

View solution in original post

9 Replies

Re: Addmonths function with a negative integer

May be this:

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

ieuyar
New Contributor III

Re: Addmonths function with a negative integer

Unfortunately, it did not work.

vinieme12
Esteemed Contributor II

Re: Addmonths function with a negative integer

try

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

Re: Addmonths function with a negative integer

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?

Highlighted
payalgosar
Contributor II

Re: Addmonths function with a negative integer

Check if field "FiscalDate" is in date format.

ieuyar
New Contributor III

Re: Addmonths function with a negative integer

This is result according to my script:

myscipt.png

And here is according to yours:

yours.png

ieuyar
New Contributor III

Re: Addmonths function with a negative integer

It did not work.

It works this way: ,

yours2.png

vinieme12
Esteemed Contributor II

Re: Addmonths function with a negative integer

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

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;

View solution in original post