cancel
Showing results for 
Search instead for 
Did you mean: 

How do I get MonthsBetween to work as documented?

Former Member
0 Kudos

According to my WebI documentation:

"MonthsBetween([Sale Date]; [Invoice Date]) returns 1 if [Sale Date] is 30 December 2005 and [Invoice Date] is 2 January 2006"

However, in WebI, the formula:

=MonthsBetween(ToDate("123005";"MMddyy");ToDate("010206";"MMddyy"))

returns 0.

This is in XI R2. My questions are as follows.

(1) Will this be fixed in a future release?

(2) What is the recommended workaround until then, for creating reports with month-by-month aging buckets? I see ways to do this, but they all involve either introducing complex modulus arithmetic or extremely verbose conversions of the form Date->Integer->String->Date.

Thanks, Eric

Edited by: Eric Hirst on Jul 30, 2008 11:04 PM, fixed typo in formula

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member

FYI, I found a solution using arithmetic that was simpler than I had previously been thinking. Instead of writing:

MonthsBetween(\[beginDate]; \[endDate])

I can write:

12 * Year(\[endDate]) + MonthNumberOfYear(\[endDate]) - 12 * Year(\[beginDate]) - MonthNumberOfYear(\[beginDate])

to match the documented behavior. A similar approach works to simulate a hypothetical QuartersBetween or YearsBetween function, similar to SQL Server's datediff with the qq or yy arguments.

-Eric

Former Member
0 Kudos

Hmmm, if this is really a documentation error and the answer to my first question is No, then what about the answer to my second? How do I create aging reports with date buckets of the form:

{ this calendar month, last calendar month, 2 calendar months ago ... }

? The same question applies to quarters and calendar years.

This is not difficult with other BI tools and RDBMS's. For example, in SQL server,

select datediff(mm, '2008-07-31', '2008-08-01')

returns 1, and in Oracle,

select MONTHS_BETWEEN(trunc(to_date('01-AUG-2008'),'mm'),trunc(to_date('31-JUL-2008'),'mm')) from dual

also returns 1. I still don't see a good way of doing this in WebI, short of using modulus arithmetic (to wrap across years), or Date->Integer->String->Date conversions to replace the day with '01', and simulate to Oracle's TRUNC(.., 'mm') option.

Edited by: Eric Hirst on Jul 31, 2008 5:53 PM

Edited by: Eric Hirst on Jul 31, 2008 6:15 PM

fritzfeltus
Product and Topic Expert
Product and Topic Expert
0 Kudos

Sorry, looks like I misunderstood your question. Let me find out whether this is a documentation error, or an issue with the function.

Former Member
0 Kudos

Hi there, this is a documentation bug since the difference between Dec 30 and Jan 2 is 0 months and 3 days. So it should give you 0. We will have to get that fixed. Thanks for the feedback...

fritzfeltus
Product and Topic Expert
Product and Topic Expert
0 Kudos

I just tried the following two formulas, and they work (return 6):


=MonthsBetween(ToDate("010108";"MMddyy");ToDate("070108";"MMddyy"))
=MonthsBetween(ToDate("010108";"MMddyy");CurrentDate())

Does this work in your environment?