Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Contributor III

## Problem with calculating months difference between 2 dates

I am using the following expression to calculate the months difference between today and the EFFECTIVE_DATE, but when the two dates are within the same month, the expression is failing instead of returning 0.  Any ideas?

year(today(2))*12)+month(today(2))) - (((year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

Thanks much!

10 Replies
Creator

Hi,

It should work. I have tested it as below.

=((year(today(2))*12)+month(today(2))) - (((year(today()-3)*12)+month(today()-3)))

which is returning zero.

You may need to verify if the Effective date is being null or any other ?

Thanks,

Hemanth.

MVP

May be try this:

=RangeSum(((year(today(2))*12)+month(today(2))), -((year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

Contributor III
Author

No, it's not returning the row with the two dates in the same month.  Strange that I can separate this expression into two and then take a difference using fabs(end date-start date) and it works.  But can't do in one expression.

This works but would rather have it in one expression and don't understand why it's not returning the row when it's in one expression.

end date:

year(today(2))*12)+month(today(2)))

start date:

year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

month difference

[end date]-[start date]

:

Contributor III
Author

disregard the fabs() on the last reply.. do not need the absolute value.

Anonymous
Not applicable

Hello,

can't you use an expression like this one?:

=num((today() - EFECTIVE_DATE)/30, '0')

Partner - Creator II

Hi!

Try this:

((year(today(2))*12)+month(today(2)))-

((year(End)*12)+month(End))

Result:

Br,

Miikka

Climber Finland

Contributor III
Author

Hemantha,

Where is the EFFECTIVE_DATE in your example?

Contributor III
Author

Jose,

This is rounding up always versus when it gets to midpoint in month.

Master

Hi Tricia,

Your expression had a lot of superfluous brackets that I've removed plus a couple that were incorrect that I've kept but remarked out to show where they were:

year(today(2))*12

// )

+month(today(2))

// )

- year(EFFECTIVE_DATE)*12+month(EFFECTIVE_DATE)

Anyway here's another expression to try (you can't have too many!)

=12*(Year(today(2)) - Year(EFFECTIVE_DATE)) + Month(today(2)) - Month(EFFECTIVE_DATE))

Community Browser