1 Reply Latest reply: Aug 8, 2017 2:10 AM by Karthik S RSS

    Cal_FY

    antony qvw

      Hi All,

       

      I have created FY Calendar based on Emp DOJ and DOL.

       

      Created caldates for each employee from DOJ to DOL

      If Emp has no DOL then consider to till date.

       

      This is working fine.

       

      But performance wise I want reduce the data.I don't want to load all the cal dates.

       

      how to accomplish this requirement?

       

      PFB for your reference with my sample data.

       

      Highlighted in Yellow(NewCalDate) is my desired output instead of load all the cal dates.

       

       

      Kindly Suggest.

        • Re: Cal_FY
          Karthik S

          Hi Antony,

           

          Please kindly use this calendar to enhance the date.

           

          ////////////////////////////////////////////////////

          //MASTER_CALENDAR_DATE///

          ////////////////////////////////////////////////////

          POLICY_END_DATE:

          LOAD

          TempDate AS DATE,

          //week(TempDate) As Week,

          YEAR(TempDate) AS DATE_Year,

          MONTH(TempDate) AS DATE_Month,

          Num(TempDate) AS DATE_Num,

          YEARTODATE(TempDate)*-1 AS DATE_CurYTDFlag, 

          YEARTODATE(TempDate,-1)*-1 AS DATE_LastYTDFlag

          //Day(TempDate) As Day,

          //'Q' & ceil(month(TempDate) / 3) AS Quarter,

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

          //WeekDay(TempDate) as WeekDay

          ;

           

           

          //=== Generate a temp table of dates ===

          LOAD

          DATE(mindate + ITERNO()) AS TempDate

          ,maxdate // Used in InYearToDate() above, but not kept

          WHILE mindate + ITERNO() <= maxdate;

           

           

          //=== Get min/max dates from Field ===/

          LOAD

          MIN(FIELDVALUE('DATE', RECNO()))-1 AS mindate,

          MAX(FIELDVALUE('DATE', RECNO())) AS maxdate

          AUTOGENERATE FIELDVALUECOUNT('DATE');

           

          Many Thanks

          Karthik