topic Generating Year Sequence Till Date in QlikView

Generating Year Sequence Till Date

Tue, 11 Mar 2014 03:44:27 GMT

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));

tresB — Tue, 11 Mar 2014 04:28:59 GMT

Try this:

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

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

er_mohit — Tue, 11 Mar 2014 04:52:09 GMT

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;

Tue, 11 Mar 2014 04:59:42 GMT

Thanks Tresesco and Mohit. It worked!