7 Replies Latest reply: Sep 2, 2014 6:40 AM by Yogesh Jamdhade RSS

    How to create calendar 1st jan 2012 to till date

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

        • Re: How to create calendar 1st jan 2012 to till date
          Jose Federico Moreno Penzo

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

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

            Is this for QlikView or Qlik Sense?

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

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



              • Re: How to create calendar 1st jan 2012 to till date
                jagan mohan rao appala

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





                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.




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

                  hi, just try this .....


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

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

                  LET vDateToday = Num(Today());





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

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

                  AUTOGENERATE 1

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





                  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 ,




                  exit Script;

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

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




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