2 Replies Latest reply: Oct 12, 2017 6:38 AM by Arvind Patil RSS

    master calendar

    ibrahibra aljefri

      hi qlikers

       

      i am loading below script and no error return but when i set YearCalander as filter only i see one record as min value

      note that Created field contains values since 2013 until today's date


      please help

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12); 

       

      Temp3: 

       

      Load 

       

       

       

       

          min(Created) as minDate, 

                     Max(Created)  as maxDate

      Resident Data; 

       

       

      Let varMinDate = Num(Peek('minDate', 0, 'Temp3')); 

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp3')); 

      DROP Table Temp3; 

       

      TempCalendar: 

      LOAD 

                      $(varMinDate) + Iterno()-1 As Num,

                      Date($(varMinDate) + IterNo() - 1) as TempDate 

                      AutoGenerate 12 While $(varMinDate) + IterNo() -1  <= $(varMinDate);

       

       

       

      MasterCalendar: 

      Load 

                     TempDate as Created , 

                     week(TempDate) As WeekCalander, 

                     Year(TempDate) As YearCalander, 

                     Month(TempDate) As MonthCalander, 

                     Day(TempDate) As DayCalander, 

                     YeartoDate(TempDate)*-1 as CurYTDFlag, 

                     YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                   inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                     ApplyMap('QuartersMap', month(TempDate), Null()) as QuarterCalander, 

                     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                     WeekDay(TempDate) as WeekDay 

      Resident TempCalendar 

      Order By TempDate ASC; 

      Drop Table TempCalendar;

        • Re: master calendar
          Arvind Patil

          Hi Ibrahibra,

           

          Please change the varibale below:

           

            AutoGenerate 12 While $(varMinDate) + IterNo() -1  <= $(varMinDate);//Require max variable


          Thanks,

          Arvind Patil

            • Re: master calendar
              Arvind Patil

              Use below script:

               

              QuartersMap:

              MAPPING LOAD 

              rowno() as Month,

              'Q' & Ceil (rowno()/3) as Quarter

              AUTOGENERATE (12);

               

              Temp3:

               

              Load

               

               

               

               

                  min(Created) as minDate,

                             Max(Created)  as maxDate

              Resident Data;

               

               

              Let varMinDate = Num(Peek('minDate', 0, 'Temp3'));

              Let varMaxDate = Num(Peek('maxDate', 0, 'Temp3'));

              DROP Table Temp3;

               

              TempCalendar:

              LOAD

                              $(varMinDate) + Iterno()-1 As Num,

                              Date($(varMinDate) + IterNo() - 1) as TempDate

                              AutoGenerate 12 While $(varMinDate) + IterNo() -1  <= $(varMaxDate );

               

               

               

              MasterCalendar:

              Load

                             TempDate as Created ,

                             week(TempDate) As WeekCalander,

                             Year(TempDate) As YearCalander,

                             Month(TempDate) As MonthCalander,

                             Day(TempDate) As DayCalander,

                             YeartoDate(TempDate)*-1 as CurYTDFlag,

                             YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

                           inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

                             date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                             ApplyMap('QuartersMap', month(TempDate), Null()) as QuarterCalander,

                             Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                             WeekDay(TempDate) as WeekDay

              Resident TempCalendar

              Order By TempDate ASC;

              Drop Table TempCalendar;