I know this but I need to get the future renewal date for each employee. let me explain this, if I was hired in 10-Nov-2010 and my contract period is two years(24 months) and my contract is renewed automatically each two years so my future renewal date or the end of contract date will be on 10-Nov-2016 as per today.
I already got this expression from here but still it shows me that my contract will be ended on 10-Nov-2018
Date(MakeDate(2017 + (-1*Even(Year(StartDate))), Month(StartDate), Day(StartDate))) as [End Of Contract]
the below excel formula is giving me the right information but I am still trying to convert that to QLikview logic to add it in my report :
However, if there is any expression on QLikview can do that, it will be great.
Have a closer look on the suggestion from swuehl which returned exact your results in the column "Formula" - you might need to adjust the StartDate within the expression to date#(StartDate, 'MM/DD/YYYY') to make sure it will be treated as a numeric value.
LOAD *, AddMonths(StartDate, MonthDiff) as NextRenewalDate;
(year(Today())*12)+month(Today())-((year(StartDate)*12)+month(StartDate))-(Day(StartDate) > Day(Today()) )
,[Contract Period Per Month]) as MonthDiff
StartDate, Contract Period Per Month,Employee, Status, Formula
2/7/2002, 24, John, Renewed, 2/7/2018
1/3/2015, 24, Sergio, Renewed, 1/3/2017
10/5/2010, 24, Michael, Renewed, 10/5/2016
9/9/2014, 24, Sara, Renewed, 9/9/2016
1/9/1990, 24, Joly, Renewed, 1/9/2018
4/17/2014, 24, Boadicea, Renewed, 4/17/2018
5/3/2014, 24, Boadicea, Renewed, 5/3/2018
6/3/2014, 24, Boadicea, Renewed, 6/3/2016
6/3/2014, 12, Boadicea, Renewed, 6/3/2016