1 Reply Latest reply: Apr 4, 2012 7:47 AM by Sunil Chauhan RSS

    Calendar Fields are missing values

    chris goudy

      My Calendar Fields are Missing values

       

      My year list box shows:

       

      2450

       

      Week Listbox is missing

       

      1

      2

      and above 45

       

      Here is my script:

       

      //-------------------------------------------------------------

      //Get Dates from Data

      //-------------------------------------------------------------

       

       

      DateTemp:

      LOAD YearMonth

      RESIDENT Transactions

      ORDER BY YearMonth Asc;

       

       

      LET vMinDate = Num(Peek('YearMonth', 0, 'DateTemp'));  //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable

      LET vMaxDate = Num(Peek('YearMonth', -1, 'DateTemp'));    

      LET vToday = Num(today());

       

       

      //-------------------------------------------------------------

      //Temporary Calendar

      //-------------------------------------------------------------

       

       

      TempCalendar:

      LOAD

      $(vMinDate)+IterNo()-1 AS Num,

      Date($(vMinDate)+IterNo()-1) AS TempDate

      AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

       

       

      //-------------------------------------------------------------

      //Master Calendar

      //-------------------------------------------------------------

       

       

      MasterCalendar:

      LEFT KEEP (Transactions)

      LOAD TempDate AS %_DateKey,

           TempDate AS %_DateKeyCA,

           week(TempDate) AS Week,

           year(TempDate) AS Year,

           month(TempDate) AS Month,

           day(TempDate) AS Day,

           weekday(TempDate) AS WeekDay,

           'Q' & ceil(month(TempDate)/3) as Quarter,

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

           week(TempDate)&'-'&Year(TempDate) AS WeekYear,

           Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

           Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

           num(month(TempDate)) as Period

      RESIDENT TempCalendar 

      ORDER BY TempDate Asc;

       

       

      Drop table TempCalendar;

      Drop table DateTemp;

        • Calendar Fields are missing values
          Sunil Chauhan

          set mean and max value according to you and use below code

           

           

          set vMaxdate=date(today());

          set vMindate=Date(AddMonths(today(),-15));

           

           

          tempCal:

          Load

          $(vMindate)+iterno()-1 as TempDate ,

          date($(vMindate)+iterno()-1) as Date

           

           

          autogenerate 1  while $(vMindate)+iterno()-1<=$(vMaxdate);

           

           

          LOAD TempDate AS %_DateKey,

               TempDate AS %_DateKeyCA,

               week(TempDate) AS Week,

               year(TempDate) AS Year,

               month(TempDate) AS Month,

               day(TempDate) AS Day,

               weekday(TempDate) AS WeekDay,

               'Q' & ceil(month(TempDate)/3) as Quarter,

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

               week(TempDate)&'-'&Year(TempDate) AS WeekYear,

               Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,

               Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,

               num(month(TempDate)) as Period

          RESIDENT TempCalendar

          ORDER BY TempDate Asc;

           

           

           

          hope this helps

           

          hope this helps