Maybe something like this:
FieldA, FieldB, ...., FieldX,
addyears(Renewal_Date, if(DayNumberOfYear(Renewal_Date)>DayNumberOfYear(PV_EDATE),0,1) + year(PV_EDATE)-year(Renewal_Date)) as Renewal_Date
If the dates in your source are string values you'll have to use the date# function to turn them into real dates: date#(Renewal_Date,'DDMMMYYYY')
Thanks for this String it seems logical, I have wriiten this statement based on your suggestion. Although I not familar with the DayNumberOfYear function. Is the number of days in the calander year?
Addyears(TempRenewalDate, if(DayNumberOfYear(TempRenewalDate)>DayNumberOfYear( PolicyVersionEndDate),0,1) + year( PolicyVersionEndDate)-year(TempRenewalDate))) as RenewalDate,
ADDYEARS (date(num(floor(InitialStartDate)),'dd/MM/yyyy'),1) asTempRenewalDate
In this example however the RenewalDate is coming out as 11/01/2014 rather than 11/01/2013
I think the string needs modifying as it doesnt take care of the -- do until greater "or equal part".
I have added an = part as such
but I am not sure is this is correct or not. It resolves this particular record.
I am however finding a lot of differences in terms of the resulting renewal date from my orignal code to that of the formula in qlikview.