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




      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.




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


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

      DROP TABLE MinMax;

      /* Master Calendar

      Disconnected during the Date Island exercise by renaming TempDate as IslandDate




      //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.