0 Replies Latest reply: Oct 18, 2017 1:38 PM by Philippe BONNIN RSS

    CY & PY AsofTAble in mastercalendar

    Philippe BONNIN

      Hi

      I try to learn about CURRENT & PREVIOUS YEARS with a AsofTable in script

       

      i have 1 tab

       

      TAB.png

       

      From another thread i imported and adapted this script

       

      [DATA]:

      LOAD Date(MakeDate([YEAR], [MONTH]), 'MM_YYYY') as MonthYear,

      [YEAR],

      [MONTH],

      [SECTORS],

      [SALES]

      FROM [lib://RANK_FLAG/DATAS.xlsx]

      (ooxml, embedded labels, table is DATA);

       

       

      AsOfTable:

      LOAD MonthYear as AsOfMonthYear,

      MonthYear,

           'CY' as Flag

      Resident [DATA];

       

      Concatenate (AsOfTable)

      LOAD MonthYear as AsOfMonthYear,

      Date(AddYears(MonthYear, -1), 'MM_YYYY') as MonthYear,

           'PY' as Flag

      Resident [DATA];

       

       

      Set vFM = 1;

       

       

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

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

      AUTOGENERATE (12); 

       

       

       

      Temp: 

      Load 

                     min(AsOfMonthYear) as minDate, 

                     max(AsOfMonthYear) as maxDate 

      Resident AsOfTable; 

       

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

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

      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 Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual fiscal year

               Dual(Month, fMonth)                as FMonth,           // Dual fiscal month

                *;

       

       

      LOAD Year + If(Month >= $(vFM), 1, 0) as fYear,         // Numeric fiscal year

           Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

           *;

       

       

      Load 

                     TempDate AS AsOfMonthYear, 

                     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 

      Resident TempCalendar 

      Order By TempDate ASC; 

      Drop Table TempCalendar; 

       

       

       

      The result is :

      RESULT.pngNO PREVIOUS VALUES :-(

       

      and this

       

      RESULT PY.png Previous = current

       

      Can you help me to understand with a right script please ?

       

      thank's by advance

       

      Philippe