How to create calendar 1st jan 2012 to till date

[#1]

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

[#2]

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;
7. Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
8. Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
9. DROP Table Temp;

For something like this:

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

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

[#3]

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

thanKS

[Mattias_Malré]

Is this for QlikView or Qlik Sense?

[sundarakumar]

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

[jagan]

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 · Accepted solution]

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;

[#8]

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

but

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