0 Replies Latest reply: Sep 16, 2015 3:59 AM by abc a RSS

    Code explanation

    abc a

      Kindly could any one explain me the ! back end Code  which already writen in ytd creation qvd

       

       

       

      LET vStartDate = Num(AddMonths(YearStart(Today()),-24));

      LET vEndDate = Num(AddMonths(YearEnd(Today()), 0));

       

       

      PERIODT:

       

       

       

      Load Distinct

        Date(MonthEnd(($(vStartDate) +(Iterno()-1))), 'MMM-YYYY') as PERIOD

      Autogenerate(1)

      While

        Date($(vStartDate)+(Iterno()-1)) <=Date($(vEndDate));

       

        PERIOD:

      Load Distinct RowNo() as PERIOD_KEY, PERIOD,

       

        Year(PERIOD) as YEAR,

        Month(PERIOD) as MONTH

       

       

       

        resident PERIODT order by PERIOD

        ;

       

        DROP table PERIODT;

       

       

       

       

      TYPE:

      LOAD TYPE_KEY,

           [TIME ANALYSIS]

      FROM

      [D:\Chinna\Work and knowledge\TYPE.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

       

       

       

       

       

       

       

       

      SUB LOAD_nAGO(vTypeTemporel, vOffset)

      REL_PER_TIME:

      LOAD PERIOD_KEY,

      PERIOD_KEY-$(vOffset) as TIME_KEY,

      '$(vTypeTemporel)' as TYPE_KEY

      RESIDENT PERIOD

      where PERIOD_KEY > $(vOffset);

      End sub

       

       

      call LOAD_nAGO('CURRENT', 0);

      call LOAD_nAGO('P-1', 1);

      call LOAD_nAGO('Y-1', 12);

       

       

       

       

       

       

      SUB LOAD_MVTOT(vTypeTemporel, vNbPeriods, vOffset);

      // F. AUNEZ, sep 2013

      // Population of the relation PERIODE - TIME

      // Creation of the moving totals (and averages ...)

      //

      Concatenate (REL_PER_TIME)

      LOAD

      PERIOD_KEY,

      PERIOD_KEY + 1 - IterNo()-$(vOffset) as TIME_KEY,

      '$(vTypeTemporel)' as TYPE_KEY

      Resident PERIOD

      while IterNo() <= $(vNbPeriods) AND PERIOD_KEY >= $(vNbPeriods)+$(vOffset)

      ;

      END SUB

      call LOAD_MVTOT ('MVTOT3', 3, 0)

      call LOAD_MVTOT ('MVTOT12', 12, 0);

      call LOAD_MVTOT ('MVTOT3 Y-1', 3, 12);

      call LOAD_MVTOT ('MVTOT12 Y-1', 12, 12);

       

       

       

       

      SUB LOAD_YTD(vTypeTemporel, vOffset);

      // F. AUNEZ, sep 2013

      // Population of the relation PERIODE - TIME

      // Creation of the YTD

      //

      //June 2013 (key 18) will totalize from January (Key 13) to June (Key 18)

      // For the YTD Year Ago, same totalization but for the Year Ago (12 keys back)

      // we will use Iter() in order to loop back until Janyary (Key 1, 13, 25 ...)

      // that what the div( ....) part does

      Concatenate (REL_PER_TIME)

      LOAD

      PERIOD_KEY,

      PERIOD_KEY + 1 - IterNo() - $(vOffset) as TIME_KEY,

      '$(vTypeTemporel)' as TYPE_KEY

      Resident PERIOD

      while PERIOD_KEY - $(vOffset)-IterNo()+1 >= div( if( mod(PERIOD_KEY- $(vOffset),

      12)=0, PERIOD_KEY- $(vOffset)-1, PERIOD_KEY- $(vOffset)), 12)*12 +1

      ;

      END SUB

       

       

      call LOAD_YTD('YTD', 0);

      call LOAD_YTD('YTD-1', 12);