Generating Year Sequence Till Date

[#1]

Hi everyone,

I am new to Qlikview and this is my first ever post here on Qlikview community.

I would like to have one table with Year values stored in it. They should start from 2000 and end with the latest year (i.e. 2014).

I want to make it dynamic, so that next year, the values would be till 2015. How do I go about it?

P.S.
I tried following but it didn't work.

SET vInitYear = 2000;

YearSequence:

LOAD vInitYear AS YearSeq AutoGenerate(Year(Today));

[tresesco · Accepted solution]

Try this:

Set vStartYear=2000;
Set vLastYear=Year(Today());

YearSeq:
Load
$(vStartYear)+RecNo() as Year
AutoGenerate $(vLastYear)-$(vStartYear);

[er_mohit]

Try this

LET vDateMin = Num(MakeDate(2000,1,1));

LET vDateMax = Floor(DayEnd(Today()));

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate ,

Year($(vDateMin) + RowNo() - 1) AS Year

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate,'DD-MM-YYYY' ) AS CalendarDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

[#4]

Thanks Tresesco and Mohit. It worked!