6 Replies Latest reply: Jan 10, 2016 9:06 PM by surfman joe RSS

    master calendar question

    surfman joe

      Hello, My sales report is weekly based. A week starts every Saturday through Friday.

       

      Based on above background, I need build up a master calendar with YTD feature. I don't need date, day, but week and year.

       

      Below is my script, but it does not work.I don't know how to have the TempWeek turn to '201501' after it reaches '201452'. Can someone help to fix it? Thanks in advance.

       

      Let varMinWeek = Num('201401'); 
      Let varMaxWeek =Num('201652');
       
      TempCalendar: 
      LOAD 
                     $(varMinWeek) + Iterno()-1 As Num, 
                     ($(varMinWeek) + IterNo() - 1) as TempWeek 
                     AutoGenerate 1 While $(varMinWeek) + IterNo() -1 <= $(varMaxWeek); 
       
      [Master Calendar]: 
      Load

                     right(TempWeek, 2) As [Week],
                     left(TempWeek, 4) as [Year],
                     YeartoDate(TempWeek)*-1 as [CurYTDFlag], 
                     YeartoDate(TempWeek,-1)*-1 as [LastYTDFlag],
                     YeartoDate(TempWeek,-2)*-1 as [Last Before Year Flag]
                    
      Resident TempCalendar 
      Order By TempWeek ASC;

      Drop Table TempCalendar;