on 07-30-2008 10:04 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry, looks like I misunderstood your question. Let me find out whether this is a documentation error, or an issue with the function.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.