6 Replies Latest reply: Feb 21, 2017 4:44 AM by Mike Slottje RSS

    QTD previous year

    Mike Slottje

      Hi guys,

       

      I'm using the following calendar for my analysis.

      SUB CALENDAR

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

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

      AUTOGENERATE (12); 

        

      LET varMinDate = num('1-1-2009');

      LET varMaxDate = num('31-12-2017');   

       

      TempCalendar: 

      LOAD

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

      Date(($(varMinDate))+Iterno()-1) AS TempDate

      AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate);

       

      MasterCalendar: 

      Load 

                     floor(date(TempDate,'DD-MM-YYYY')) AS %key_Date,

                     TempDate AS Date,

                     week(TempDate) As Week, 

                     Year(TempDate) As Year, 

                     Month(TempDate) As Month, 

                     Day(TempDate) As Day, 

                     //YeartoDate(TempDate)*-1 as CurYTDFlag, 

                     //YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

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

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

                     WeekDay(TempDate) as WeekDay,

                     DayNumberOfYear(TempDate) as DayoftheYear,

                     If( DayNumberOfYear(TempDate) <= DayNumberOfYear(Today()), 'Y', 'N' ) as IsInYTD,

                     If( DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()), 'Y', 'N') as IsInQTD,

            If( Day(TempDate) <= Day(Today()), 'Y', 'N') as IsInMTD,

            If( Month(TempDate) = Month(Today()), 'Y', 'N') as IsCurrentMonth,

            If( Month(AddMonths(TempDate,1)) = Month(Today()), 'Y', 'N') as IsLastMonth

      Resident TempCalendar 

      Order By TempDate ASC;

       

      Drop Table TempCalendar; 

      END SUB;

       

      To compare our sales from current month current year vs current month previous year, I used the following script:

      SUM(Date ={">=$(=date(addmonths(MonthStart(Today()),-12))) <=$(=date(addmonths(MonthEnd(Today()),-12)))"}[Nett Amount])

       

      Now I want to do the same with quarters instead of months, but the function addquarters does not exist.

      Could you guys explain me how to do this comparison?

       

      Thanks a lot in advance!

      Mike