3 Replies Latest reply: Nov 21, 2013 5:42 AM by Brian Taylor RSS

    Use of LastYTDFlag

    Brian Taylor

      I have a calendar using the script below

       

      MinMax:

      LOAD

      Min(Date) as MinDate,

      Max(Date) as MaxDate

      RESIDENT Facts;



      LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);



      /*  Temporary Calendar



      Generates a single table with one field containing

      all existing dates between MinDate and MaxDate.



      */




      TempCal:

      LOAD

      date($(vMinDate) + rowno() - 1) AS TempDate

      AUTOGENERATE

      $(vMaxDate) - $(vMinDate) + 1;



      DROP TABLE MinMax;



      /* Master Calendar



      Disconnected during the Date Island exercise by renaming TempDate as IslandDate



      */




      MasterCalendar:

      LOAD

      //TempDate as IslandDate,

      TempDate AS Date,

      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,

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

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

      RESIDENT TempCal

      ORDER BY TempDate ASC;



      DROP TABLE TempCal;

      Max date in my data is 19/11/2013 so I would like any transactions with a date after 19/12/2012 not to be flagged as LastYTDFlag 1 but 0

      Dates up to 31/12/2012 are being flagged as 1 but they should be 0

      The date filed in my data is loaded as

       

      inception_date_tpi as Date,

       

      Can anyone adice what I need to change in My Calendar for this to work.