topic Re: End Of Contract Formula in QlikView

End Of Contract Formula

Anonymous — Wed, 04 May 2016 20:27:23 GMT

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

Re: End Of Contract Formula

sunny_talwar — Wed, 04 May 2016 20:37:10 GMT

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);

Re: End Of Contract Formula

sunny_talwar — Wed, 04 May 2016 20:39:57 GMT

This one should also work:

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

Re: End Of Contract Formula

swuehl — Wed, 04 May 2016 22:03:43 GMT

Another (shorter) option would be

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

Re: End Of Contract Formula

Anonymous — Thu, 05 May 2016 05:04:44 GMT

Thank you so much ..

that's what I was looking for.

Thanks,

Ibrahim

Re: End Of Contract Formula

oknotsen — Thu, 05 May 2016 14:34:34 GMT

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.