24 Replies Latest reply: Jun 28, 2015 5:16 PM by sahasra ram RSS

    curYTD flag

      Hi Friends,

       

      Can anyone please correct me for the curYTD flag calculation code I am getting Flag 0 for all the dates. Is that correct or do I need to change something.

       

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12);       

      Temp: 

      Load 

      min (date(OrderDate)) as minDate, 

      max (date(OrderDate)) as maxDate

      Resident Orders;

      drop table  Orders;   

      Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

      Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

      LET vDateToday = Num(Today());

        DROP Table Temp;

      TempCalendar: 

      LOAD 

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

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

      AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

           

      MasterCalendar:

        Load 

        date(TempDate) AS OrderDate, 

         week(TempDate) As Week, 

         Year(TempDate) As Year, 

         Month(TempDate) As Month,

        num( Month(TempDate)) as MonthNo,

      Month(TempDate)*100+Day(TempDate) as DayOfYear,

        // Month(TempDate)&'-'& Year(TempDate) as MonthYear,

         Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,

         Year(TempDate) &Num(Month(TempDate)) as YearMonth,

         //if(Year(TempDate)=WeekYear(TempDate), Week(TempDate)-Week(MonthStart(TempDate)) +1, Week(TempDate)+52 -Week(MonthStart(TempDate)) +1) as WeekinMonth,

         //'Week' & Ceil(Day(TempDate)/7) as WeekInMonth,

         Ceil(Day(TempDate)/7) as WeekInMonth,

         Day(TempDate) As Day, 

         //InYearToDate(TempDate, $(vToday), 0) *-1 as CurYTDFlag,

         //InYearToDate(TempDate, $(vToday), -1) *-1 as LastYTDFlag,

        ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

        Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

        WeekYear(TempDate)& Num(week(TempDate)) as YearWeek,

        WeekDay(TempDate) as WeekDay,

        Num(WeekDay(TempDate)+1) as WeekDayNo,

        Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTD_Flag,

        Year2Date(TempDate,-1, 1, $(vToday))*-1 AS   LastYTD_Flag,

        InMonthToDate(TempDate, 0, 1, $(vToday))*-1 AS CurMTD_Flag,

        InMonthToDate(TempDate,-1, 1, $(vToday))*-1 AS LastMTD_Flag

        Resident TempCalendar 

        Order By TempDate; 

      Drop Table TempCalendar;

       

       

      store MasterCalendar into [$(vQVDpath)MasterCalendar.qvd] (qvd);

      drop table MasterCalendar;