Qlik Community

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
munna1317
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:

LOAD

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

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

AUTOGENERATE 1

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

Calendar:

LOAD

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,

Date(MonthStart(AddMonths(TempDate,-12))) as CalendarMonthStartSTLY,

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:
  2. Load
  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
Community Manager

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

Is this for QlikView or Qlik Sense?

I accept cookies.
sundarakumar
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]:

LOAD DISTINCT

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]

;

LOAD DISTINCT

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

AUTOGENERATE (1)

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

-Sundar

MVP
MVP

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

Hi Yogesh,

Check this link below

Fiscal and Standard Calendar generation

Try this script below

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

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

FiscalCalendar:

LOAD

*,

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, 

LOAD

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.

munna1317
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:

LOAD

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

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

AUTOGENERATE 1

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

Calendar:

LOAD

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,

Date(MonthStart(AddMonths(TempDate,-12))) as CalendarMonthStartSTLY,

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!!!!!!!!!!!!