9 Replies Latest reply: Sep 14, 2017 2:18 PM by Tiffani Leslie RSS

    Assigning a Rolling 12 Month Flag to an AutoGenerated calendar

    Tiffani Leslie

      Our company has a script that includes an autogenerated calendar that spans between a min and max date.


         $(vMinDate) + Iterno()-1 As Num, 

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

         AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate); 


      Each date is then assigned various calendar dimensions including a flag to indicate which rolling 12 month window in which it belongs.  This recreates itself each night during our reload process.


          Dual(Text(Date("%Date Key",'YYYY-MMM')),Num(MonthStart("%Date Key"))) as "%Year-Month-R12",

          Addmonths("%Date Key", -1-(IterNo()-1)) as "%Date Key"

      FROM [$(vQvdSchemaPath)/Periods Dim.qvd] (qvd)

      WHILE IterNo() <= 12;


      I've noticed that this only works 100% if the current month has 31 days.  If the current month does not, such as September, only months with 31 days will have a value populated in "%Year-Month-R12". 


      Flag 0.PNG



      I fixed this problem by setting the mode in the AddMonths() function to 1.  However, now I am having an issue with values not populating for the 28th day of each month that has 31 days.  What am I missing?


      Flag 1.PNG