Cal_FY

[antonybi]

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.

[karthikoffi27se]

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