Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

antonybi
New Contributor III

Cal_FY

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.

Tags (1)
1 Reply
karthikoffi27se
Contributor III

Re: Cal_FY

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

Community Browser