Differences in Years and in Months

[beck_bakytbek]

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

[sunny_talwar]

Is this right?

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

[sunny_talwar]

Something like this

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

Table:

LOAD *,

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

LOAD *,

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

];

[beck_bakytbek]

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

[beck_bakytbek]

Hi Sunny,

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

[sunny_talwar]

Can you point out what is not right here?

[beck_bakytbek]

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?

[sunny_talwar]

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

[beck_bakytbek]

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

[sunny_talwar · Accepted solution]

May be this

test:

LOAD *,

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

load *,

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

];