Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
You can do it just as you do it on QlikView 11. Take a look at this thread:
The only thing you need to change is you need to replace this code:
For something like this:
Let varMinDate = date('01/01/2012');
Let varMaxDate = date(Now()); //or date(Today());
hi....jose iit does not work>>>>
thanKS
Is this for QlikView or Qlik Sense?
//--- 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
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.
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;
hi..........it does get desired result!!!!!!!!!!!!!!
but
thanks its improve my concept!!!!!!!!!!!!