End Of Contract Formula

[Anonymous]

Dears,

I hope you are well,

I want to get the End Of Contract result for each employee as shown in the below table:

Employee	StartDate	Contract Period	Status	End Of Contract
John	2/7/2002	2	Renewed	2/7/2018
Sergio	1/3/2015	2	Renewed	1/3/2017
Michael	10/5/2010	2	Renewed	10/5/2018
Sara	1/9/2001	2	Renewed	1/9/2017
Joly	1/9/1990	2	Renewed	1/9/2018
Boadicea	4/17/2014	2	Renewed	4/17/2018

The formula for end of contract is :

=DATE(YEAR(B2) + DATEDIF(B2, DATE(2017, MONTH(B2), DAY(B2)), "Y") + ISEVEN(YEAR(B2)), MONTH(B2), DAY(B2))

my issue is that I need to convert this formula to Qlikview logic so I can show this report in QLikview. Or if there is any other formula can do the same that would be fine. 

Can anyone help me out here.

Thanks,

Ibrahim

[sunny_talwar · Accepted solution]

Try this may be:

Table:

LOAD Employee,

     StartDate,

     [Contract Period],

     Status,

     Date(MakeDate(2017 + (-1*Even(Year(StartDate))), Month(StartDate), Day(StartDate))) as [End Of Contract]

FROM

[https://community.qlik.com/thread/215657]

(html, codepage is 1252, embedded labels, table is @1);

[sunny_talwar]

This one should also work:

Date(AddYears(StartDate, 2017 - Year(StartDate) + (-1*Even(Year(StartDate))))) as [End Of Contract1]

[swuehl]

Another (shorter) option would be

  SetDateYear(StartDate, 2017-Even(Year(StartDate))) as [End Of Contract2]

[Anonymous]

Thank you so much ..

that's what I was looking for.

Thanks,

Ibrahim

[oknotsen]

If your question is now answered, please flag the Correct Answer.

If not, please make clear what part of this topic you still need help with.

Please do not start new questions in the same topic.