Qlik Community

QlikView Management

Discussion Board for collaboration on QlikView Management.

kash04kk
New Contributor II

Autogenerate quarters

Hi everyone,

I would like to autogenerate the quarter end of the dates. Currently, i'm using them in script like this:

load * inline

[valuation_date

3/31/2011

6/30/2011

9/30/2011

12/31/2011

3/31/2012

6/30/2012

9/30/2012

12/31/2012

3/31/2013

6/30/2013

9/30/2013

12/31/2013

3/31/2014

6/30/2014

9/30/2014

12/31/2014

3/31/2015

];

Thanks and Regards,

Kashif

1 Solution

Accepted Solutions

Re: Autogenerate quarters

Or maybe like this :

set vStartDate=date(date#('3/31/2011','M/DD/YYYY')) ;

set vNoQuarters = 17 ;

QuarterEnd:

Load

  addmonths($(vStartDate),(iterno()-1)*3) as QuarterEnd

autogenerate 1  while iterno()<= $(vNoQuarters)

;

7 Replies

Re: Autogenerate quarters

Use QuarterEnd() Function

try below script in QlikView

LOAD *,

Year(Date) as Year,

QuarterStart(Date) as QuarterStart,

QuarterEnd(Date) as QuarterEnd;

LOAD date(MakeDate(2013)+IterNo()-1) as Date,

ceil(Rand()*100+1000) as Amount

AutoGenerate(1)

While MakeDate(2013)+IterNo()-1<=Today();

Re: Autogenerate quarters

Or maybe like this :

set vStartDate=date(date#('3/31/2011','M/DD/YYYY')) ;

set vNoQuarters = 17 ;

QuarterEnd:

Load

  addmonths($(vStartDate),(iterno()-1)*3) as QuarterEnd

autogenerate 1  while iterno()<= $(vNoQuarters)

;

kash04kk
New Contributor II

Re: Autogenerate quarters

Hey Bill,


This script worked. Thanks a lot!

Can you explain briefly what does iterno() mean?

Re: Autogenerate quarters

The while iterno()<= $(vNoQuarters) means that each record will be evaluated until iterno() is greater than $(vNoQuarters).

During these iterations iterno() returns 1 for the first record and then increments by 1 for each record, i.e. it acts as a counter.

kash04kk
New Contributor II

Re: Autogenerate quarters

will iterno() always return 1 for the first record?

Re: Autogenerate quarters

Yup.

See entry from QV Desktop Help I have pasted below.

IterNo( )

This function is only meaningful if used together with a while clause, see Load. IterNo( ) returns an integer indicating for which time one single record is evaluated in a load statement with a while clause. The first iteration has number 1.

kash04kk
New Contributor II

Re: Autogenerate quarters

Thanks a lot Bill. Really appreciate the help.

Community Browser