topic To create a Calendar for a Given Max & Min Date in QlikView

To create a Calendar for a Given Max & Min Date

Wed, 18 Jun 2014 10:20:04 GMT

Hi everybody,

I have Max_Date 30/06/2006 as Min_Date 08/12/2005

now my task is to create all dates between these dates.. Please help me out if anyone has any idea.

and one more thing how I add these dates as calendar object on the dashboard like

Thank you in advance.

Re: To create a Calendar for a Given Max & Min Date

MK_QSL — Wed, 18 Jun 2014 10:48:07 GMT

MinMaxDate:

Load

NUM(Date('08/12/2005','DD/MM/YYYY')) as MinDate,

NUM(Date('30/06/2006','DD/MM/YYYY')) as MaxDate

AutoGenerate 1;

Let vMinDate = NUM(PEEK('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxDate'));

Calendar:

Load

Date(DateNum) as Date,

Month(DateNum) as Month,

Year(DateNum) as Year;

Load

$(vMinDate) + IterNo() - 1 as DateNum

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Now create a slider/Calendar object with Input Style is Calendar and Field is Date as below

Re: To create a Calendar for a Given Max & Min Date

Wed, 18 Jun 2014 10:49:11 GMT

Creating A Master Calendar

Re: To create a Calendar for a Given Max & Min Date

Anonymous — Wed, 18 Jun 2014 10:53:09 GMT

In many cases it would be weird to use existing max and min dates for the calendars. Calendars are better to start from the beginning of a year and end at the end of the year. So, a more natural approach is to use the year start of the min date as calendar start, and year end of the max date as the calendar end.

Re: To create a Calendar for a Given Max & Min Date

its_anandrjs — Wed, 18 Jun 2014 11:27:30 GMT

You have to create a master calendar in the data model see the script below but if you need for particular dates then change the max min variables

Let varMinDate = Num(YearStart(Today()));

Let varMaxDate = Num(YearEnd(Today()));

//Let varMinDate = NUM(Date#('08/12/2005','DD/MM/YYYY'));

//Let varMaxDate = NUM(Date#('30/06/2006','DD/MM/YYYY'));

TempDateTable:

LOAD

date($(varMinDate)+IterNo()-1) AS TempDatefield

AutoGenerate 1

WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

MasterCalendar:

LOAD

TempDatefield,

Month(Floor(TempDatefield)) AS Floordate,

Year(date(TempDatefield)) as Year,

Month(date(TempDatefield)) as Month,

Day(date(TempDatefield)) as Day,

Time(date(TempDatefield)) as Time

Resident TempDateTable;

DROP Table TempDateTable;