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


    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 Selvaraj

          Hi Antony,


          Please kindly use this calendar to enhance the date.







          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 ===


          DATE(mindate + ITERNO()) AS TempDate

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

          WHILE mindate + ITERNO() <= maxdate;



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


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

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



          Many Thanks