4 Replies Latest reply: Oct 12, 2010 11:01 AM by Blaze101 RSS

    Trouble with master calendar

      Try to execute this script, but execution failed (Field not found <TD>

      set TD=today();
      set BData=MonthStart(addmonths(TD,-2));
      set EData=MonthEnd(addmonths(TD,-1));
      set BBData=MonthStart(addmonths(TD,-13));
      set EEData=MonthEnd(addmonths(TD,-13));

      t1:
      LOAD date($(Bdata) + IterNo()) as aDate
      AUTOGENERATE 1
      WHILE $(BData) + IterNo() <= $(Edata);


      LOAD date($(BBdata) + IterNo()) as aDate
      AUTOGENERATE 1
      WHILE $(BBData) + IterNo() <= $(EEdata);

      Whats wrong un my script?

        • Trouble with master calendar

          try this maybe:

          LET TD=today();
          set BData=MonthStart(addmonths(TD,-2));
          set EData=MonthEnd(addmonths(TD,-1));
          set BBData=MonthStart(addmonths(TD,-13));
          set EEData=MonthEnd(addmonths(TD,-13));

          t1:
          LOAD date($(Bdata) + IterNo()) as aDate
          AUTOGENERATE 1
          WHILE $(BData) + IterNo() <= $(Edata);


          LOAD date($(BBdata) + IterNo()) as aDate
          AUTOGENERATE 1
          WHILE $(BBData) + IterNo() <= $(EEdata);

            • Trouble with master calendar

              I replace all "set" for "let', but still no works! load becomes endless.... (in debug mode variables BData and EData is NULL!

                • Trouble with master calendar

                  Try this then. replace the '???' with the transaction date or any other date you wish to autogenerate from...

                   

                   

                  //*************** Temporary Calendar ***************

                  DateTemp:

                  LOAD ???

                  RESIDENT TransactionalData

                  ORDER BY ??? Asc;

                   

                  LET vMinDate = Num(Peek('???', 0, 'DateTemp'));

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

                  LET vToday = Num(today());

                   

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

                  LOAD TempDate AS Date,

                  Date(Date#(TempDate,'DD/MM/YYYY'),'DD/MM/YYYY') as ???,

                  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,

                  num(month(TempDate)) as Period,

                  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

                  RESIDENT TempCalendar

                  ORDER BY TempDate Asc;

                   

                  Drop Table TempCalendar;

                  Drop Table DateTemp;



                  • Trouble with master calendar

                    Hey buddy.

                     

                    Did this work for you?