Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

ibraheem_qaed
New Contributor II

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:

   

EmployeeStartDateContract PeriodStatusEnd Of Contract
John2/7/20022Renewed2/7/2018
Sergio1/3/20152Renewed1/3/2017
Michael10/5/20102Renewed10/5/2018
Sara1/9/20012Renewed1/9/2017
Joly1/9/19902Renewed1/9/2018
Boadicea4/17/20142Renewed4/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

1 Solution

Accepted Solutions

Re: End Of Contract Formula

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


Capture.PNG

5 Replies

Re: End Of Contract Formula

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


Capture.PNG

Re: End Of Contract Formula

This one should also work:

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

MVP
MVP

Re: End Of Contract Formula

Another (shorter) option would be

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

ibraheem_qaed
New Contributor II

Re: End Of Contract Formula

Thank you so much ..

that's what I was looking for.

Thanks,

Ibrahim

oknotsen
Honored Contributor III

Re: End Of Contract Formula

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.

May you live in interesting times!