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

               

              -Sundar

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

                 

                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.

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

                   

                   

                  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;

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

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

                     

                    but

                     

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