10 Replies Latest reply: Feb 15, 2018 9:57 AM by beck bakytbek

# Differences in Years and in Months

Hi Folks, i have a question:

my table does look like:

Project     ReportDate     EndDate      Difference in Months

A            31.03.2018     30.06.2021          39 Months

I am using this function:

step1 : SET MonthDiff = Num(((year(\$2) * 12) + month(\$2))-(((year(\$1) * 12) + month(\$1))) + 1)

step2: \$(MonthDiff(ReportDate, EndDate)) AS MonthsDifference

with this Function i can calculate only differences as interval 40 Months.

My expected Output is

Project

A

Restmonth this Year   = 9

Restmonth this Year + 1 Year = 12

Restmonth this Year + 2 Years = 18

Sum: 39 Months

Does anybody have any idea how to resolve this issue?

Thanks a lot

Beck

• ###### Re: Differences in Years and in Months

Is this right?

Shouldn't it be 12 for Year + 2 and then 6 for Year + 3?

• ###### Re: Differences in Years and in Months

Something like this

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

Table:

\$(MonthDiff(Start, End)) AS MonthsDifference;

Date(RangeMax(ReportDate + 1, YearStart(ReportDate, IterNo() - 1))) as Start,

Date(RangeMin(EndDate, YearStart(ReportDate, IterNo())-1)) as End

While YearStart(ReportDate, IterNo() - 1) <= EndDate;

LOAD * INLINE [

Project, ReportDate, EndDate

A, 31.03.2018, 30.06.2021

];

• ###### Re: Differences in Years and in Months

Hi Sunny,

i have implemented this, but it does look a bit strange. i attached the qfv, can you take a look?

• ###### Re: Differences in Years and in Months

Can you point out what is not right here?

• ###### Re: Differences in Years and in Months

Hi Sunny,

i've analysed, everything is correct, my only question is, what if, if i have more then 100 projects, then the field project will be multiplied. is that correct?

• ###### Re: Differences in Years and in Months

Yes it will... but isn't that what you wanted to see?

• ###### Re: Differences in Years and in Months

i wanted only the calculation (differences in year and month), is that possible to avoid the multiplication of field: project?

• ###### Re: Differences in Years and in Months

May be this

test:

\$(MonthDiff(Start, EndDate)) AS MonthsDifference;

MonthStart(ReportDate-1, 1) as Start

inline [

Projekt, ReportDate, EndDate

A, 31.03.2018, 30.06.2021

B, 20.02.2017, 30.06.2021

C, 20.03.2016, 30.06.2021

];

• ###### Re: Differences in Years and in Months

Hi Sunny,

thanks a lot for your help and your time, it does look great, this is the sollution for my issue.

Thanks a lot

• ###### Re: Differences in Years and in Months

Hi Suny,

thanks a lot for your responce and your time,

yes you right, but if you see the Monthdifference between is

ReportDate     EndDate      Difference in Months

31.03.2018     30.06.2021          39 Months

9 + 12 + 18 = 39