4 Replies Latest reply: Sep 12, 2014 3:07 PM by Steve Zagzebski RSS

    InYearToDate not working properly

    Steve Zagzebski


      I can't get my Inyeartodate function to work properly. When I do the following the "YTD" flag below is givinh me date from the year 1906!

       

       

      vYTD = date(Floor(MonthEnd(today(),-1)),'YYYY-MM-DD');

      D_CALENDAR_DATE_PRE1:
      LOAD
      date(CALENDAR_DT,'YYYY-MM-DD') as Standard_Date
      FROM
      $(vInputFile1)(qvd);

      D_CALENDAR_DATE:
      NoConcatenate
      LOAD *,
      If(InYearToDate(Standard_Date,$(vYTD),1),1,0) as YTD
      Resident D_CALENDAR_DATE_PRE1;

       

      Thanks in advance,

      Steve

       

        • Re: InYearToDate not working properly
          Massimo Grossi

          DIRECTORY;

           

          // generate a test calendar and store

          Calendar:

          load

            date(makedate(2013,1) + rowno()) as CALENDAR_DT

          AutoGenerate 1000;

          store Calendar into Calendar.qvd (qvd);

          DROP Table Calendar;

           

          // your script starts here

          let vYTD = date(Floor(MonthEnd(today()),-1),'YYYY-MM-DD');

           

          D_CALENDAR_DATE_PRE1:

          LOAD

          date(CALENDAR_DT,'YYYY-MM-DD') as Standard_Date

          FROM

          Calendar.qvd (qvd);

           

          D_CALENDAR_DATE:

          NoConcatenate

          LOAD *,

          If(InYearToDate(Standard_Date,'$(vYTD)',-1),1,0) as YTD

          Resident D_CALENDAR_DATE_PRE1;

          • Re: InYearToDate not working properly

            let vYTD = date#(Floor(MonthEnd(today(),-1)),'YYYY-MM-DD');

             

            //----   Also an option

            //let vYTD2 = Date#(MonthsStart(1,Today(),0)-1);

             

            D_CALENDAR_DATE_PRE1:

            LOAD

            date(CALENDAR_DT,'YYYY-MM-DD') as Standard_Date,

            InYearToDate(Date(CALENDAR_DT),$(vYTD),0)*-1 as YTD

             

            FROM $(vInputFile1)(qvd);

             

             

            With this solution, the InYearToDate returns -1 (true) or 0 (false) if CALENDAR_DT is between 1/1/<Current Year> and the last day of the previous month.

             

            You don't need to load two tables for the same data. The last 1 in InYearToDate(Standard_Date,$(vYTD),1) is not a valid number, has to be between 2 and 12 or 0.

             

            ex.

            vYTD = 9/12/2014

            CALENDAR_DT= 12/1/2013

            YTD = 0

             

            vYTD = 9/12/2014

            CALENDAR_DT= 1/5/2014

            YTD = 1

             

            vYTD = 9/12/2014

            CALENDAR_DT= 10/5/2014

            YTD = 0

             

            Hope this helps!