3 Replies Latest reply: Mar 13, 2018 7:55 PM by Yuji Yamane RSS

    Rolling 12 Variable

    Darren Jones

      Hello All,


      I am trying to create a variable in my script for a rolling 12 calculation. I thought I had this figured out, but I cannot get it to work. See my calendar script below, any insight would be much appreciated.


      //Calendar Tab


      /*************** MinMax Table *************



      Keeps minimum and maximum Date value from Facts/ Salesdata table








             Min(Date) as MinDate,

             Max(Date) as MaxDate

      RESIDENT Salesdata;



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

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

      LET vToday = $(vMaxDate);





      let vMaxDay   = '=day(max(Date))';

      let vMaxMonth  = '=month(max(Date))';

      let vMaxYear  = '=max(Year)';

      let vPriorMonth  = '=month(addmonths(max(Date),-1))';

      let vPriorMonthYear = '=Year(addmonths(max(Date),-1))';

      let vPriorYear  = '=vMaxYear-1';

      let vPriorYearDate = '=date(addyears(max(Date),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';

      let vPriorYear2  = '=vMaxYear-2';



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






             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), 'MM-YYYY') AS MonthYear,

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

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

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

            If(Num((TempDate))>=(Num(MonthStart(date($(varMaxDate)),-12))),1,0) as R12Flag

      RESIDENT TempCal

      ORDER BY TempDate ASC;



      DROP TABLE TempCal;