Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
triciagdaly
Contributor III
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
hemhund2016
Creator
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.

sunny_talwar

May be try this:

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

triciagdaly
Contributor III
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.

screen shot.PNG

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]

:

triciagdaly
Contributor III
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')

miikkaqlick
Partner - Creator II
Partner - Creator II

Hi!

Try this:

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

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

Result:

Br,

Miikka

Climber Finland

triciagdaly
Contributor III
Contributor III
Author

Hemantha,

Where is the EFFECTIVE_DATE in your example?

triciagdaly
Contributor III
Contributor III
Author

Jose,

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

effinty2112
Master
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))