5 Replies Latest reply: May 5, 2016 10:34 AM by Onno van Knotsenburg

# End Of Contract Formula

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

Try this may be:

Table:

StartDate,

[Contract Period],

Status,

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

FROM

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

• ###### Re: End Of Contract Formula

This one should also work:

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

• ###### Re: End Of Contract Formula

Another (shorter) option would be

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

• ###### Re: End Of Contract Formula

Thank you so much ..

that's what I was looking for.

Thanks,

Ibrahim