Topics Map > University of Chicago > IT Services > Business Systems > Reporting Systems
Business Objects - WebI - Calculating the Number of Months for a Date Range
This article explains how to create a variable that calculates the number of months between a start and end date. Normally used for calculating a monthly average or as a multiplier for projections.
This article explains how to create a variable that calculates the number of months between a start and end date. You can use this for calculating a monthly average or as a multiplier for projections.
Open your report and click Design on the menu bar in the upper-right corner to enter design mode.
If you are using dates from prompts:
On the Reporting Elements tab, insert pre-defined cells with the date prompts into the header of the report.
Open Notepad in the Windows Start menu. Copy and paste the formulas from the cells to the Notepad document.
Create a new variable in your report:
On the Data Access menu, select New Dimension from the New Variable drop-down list under the Data Objects tab.
Enter the name for your new variable. Ensure your variable name is meaningful and tells you what the data is. You can change it later if you need to.
Select Dimension from the Qualification drop-down list.
- Click the Formula tab and enter the formula you need.
For YYYYMM formatted dates (Character):=MonthsBetween(toDate( copy your start date formula here;"yyyymm"); todate( copy your end date formula here;"yyyymm") )
For MM/DD/YYYY formatted dates:
=MonthsBetween( insert start date field here;insert end date field here)
Click the OK button to create the new variable.
This month count can be used to calculate the monthly average expense for a range of months returned by the data provider. It can also be used as a multiplier for calculating a projection if the end date is a point in the future.
Business Objects calculates the return value for Monthsbetweeen (n) by:
- Determining the number of days in the month of first_date.
- Adding this number of days n times to first_date until the resulting date is the same as or later than last_date.
- Returning n if the result is the same as the last_date, n-1 if later than last_date.
This means that if your date range goes from the end of a month to the end of the following month (in the same or any subsequent year as the year of the first month), and the following month has fewer days, Business Objects returns a value of n which is one month shorter than expected.