Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
This one should also work:
Date(AddYears(StartDate, 2017 - Year(StartDate) + (-1*Even(Year(StartDate))))) as [End Of Contract1]
Another (shorter) option would be
SetDateYear(StartDate, 2017-Even(Year(StartDate))) as [End Of Contract2]
Thank you so much ..
that's what I was looking for.
Thanks,
Ibrahim
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.