# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## How to create calendar 1st jan 2012 to till date

How to create calendar 1st jan 2012 to till date??????

1 Solution

Accepted Solutions
Contributor II

## Re: How to create calendar 1st jan 2012 to till date

hi, just try this .....

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

LET vDateMax = Num(MakeDate(2001,12,31));

LET vDateToday = Num(Today());

TempCalendar:

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

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

AUTOGENERATE 1

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

Calendar:

Date(TempDate) AS [Calendar Date],

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS Month,

num(Month(TempDate)) AS MonthNum,

Num(Num(Year(TempDate))&if(Num(Month(TempDate))<10,0)&Num(Month(TempDate)))  as CalendarYYYYMM,

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

Year(TempDate) AS Year,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

Date(MonthStart(TempDate)) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

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

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

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Regards ,

Harish

exit Script;

7 Replies
Not applicable

## Re: How to create calendar 1st jan 2012 to till date

You can do it just as you do it on QlikView 11. Take a look at this thread:

Creating A Master Calendar

The only thing you need to change is you need to replace this code:

1. Temp:
3.                min(OrderDate) as minDate,
4.                max(OrderDate) as maxDate
5. Resident Orders;
6. Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
7. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
8. DROP Table Temp;

For something like this:

Let varMinDate = date('01/01/2012');

Let varMaxDate = date(Now()); //or date(Today());

Not applicable

## Re: How to create calendar 1st jan 2012 to till date

hi....jose iit does not work>>>>

thanKS

Community Manager

## Re: How to create calendar 1st jan 2012 to till date

Is this for QlikView or Qlik Sense?

Valued Contributor II

## Re: How to create calendar 1st jan 2012 to till date

//--- Assign the start and end dates to variables
Let MinDate=num(makedate(2012,1,1));
Let MaxDate=num(today());

[Master Calendar]:

Temp_Date as submit_date_alone_num,

Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

Year(Temp_Date) as [Year],

Month(Temp_Date) as [Month],

day(Temp_Date) as Day,

Date(Temp_Date, 'YYYY-MM') as [Year - Month],

'Q' &
Ceil(Month(Temp_Date) / 3) as [Quarter]

;

date(\$(vMinDate) + IterNo() - 1) as Temp_Date

AUTOGENERATE (1)

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

-Sundar

MVP

## Re: How to create calendar 1st jan 2012 to till date

Hi Yogesh,

Fiscal and Standard Calendar generation

Try this script below

LET vStartDate = Num(YearStart(Today(), -2));

LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:

*,

Year(Date) AS Year,

Month(Date) AS Month,

Date(MonthEnd(Date), 'MMM') AS MonthName,

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,

Date(\$(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Hope this helps you.

Regards,

Jagan.

Contributor II

## Re: How to create calendar 1st jan 2012 to till date

hi, just try this .....

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

LET vDateMax = Num(MakeDate(2001,12,31));

LET vDateToday = Num(Today());

TempCalendar:

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

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

AUTOGENERATE 1

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

Calendar:

Date(TempDate) AS [Calendar Date],

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS Month,

num(Month(TempDate)) AS MonthNum,

Num(Num(Year(TempDate))&if(Num(Month(TempDate))<10,0)&Num(Month(TempDate)))  as CalendarYYYYMM,

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

Year(TempDate) AS Year,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

Date(MonthStart(TempDate)) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

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

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

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Regards ,

Harish

exit Script;

Not applicable

## Re: How to create calendar 1st jan 2012 to till date

hi..........it does get desired result!!!!!!!!!!!!!!

but

thanks its improve my concept!!!!!!!!!!!!