Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
Is this right?
Shouldn't it be 12 for Year + 2 and then 6 for Year + 3?
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
];
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
Hi Sunny,
i have implemented this, but it does look a bit strange. i attached the qfv, can you take a look?
Can you point out what is not right here?
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?
Yes it will... but isn't that what you wanted to see?
i wanted only the calculation (differences in year and month), is that possible to avoid the multiplication of field: project?
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
];