To create a Calendar for a Given Max & Min Date

[#1]

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.

[MK_QSL · Accepted solution]

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

[#3]

Creating A Master Calendar

[Anonymous]

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.

[its_anandrjs]

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

or

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