Calculate difference in months and then AddMonths

[#1]

Hi,

I'm trying to count the number of months between a date and today AND then if the result is over 12 months, add 12 months to create a new date.

In my script I have set the following variable:
 

SET MonthDiff=((year(today(2))*12)+month(today(2))) - (((year($1)*12)+month($1)));

In my table I have the following expression to begin with.  I will build this up to vary for 24 months, 36 etc...

if (count({<($(MonthDiff(StartDate)) = {'>12'}>}), AddMonths(StartDate, 12), 0))

The components on their own work - monthdiff gives me the expected results, and AddMonths works too.  However, combined above it is giving me blanks.

Thanks for any help.

Cheers.

[jagan · Accepted solution]

Hi,

Try like this

LOAD

*,

if ($(MonthDiff(StartDate)) > 12, AddMonths(StartDate, 12), 0)) AS NewDate

FROM DataSource;

Regards,

Jagan.

[#3]

Thanks Jagan - that worked.

How do I change it to more than 12 but less than 24??

Cheers.

[jagan]

Hi,

Try like this

LOAD

*,

if ($(MonthDiff(StartDate)) > 12 AND $(MonthDiff(StartDate)) < 24, AddMonths(StartDate, 12), 0)) AS NewDate

FROM DataSource;

Regards,

Jagan.