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.

  1. Open your report and enter the Design mode by clicking on Design on the menu bar in the upper-right corner.
  2. If you are using dates from prompts:
    1. On the Reporting Elements tab, insert pre-defined cells with the date prompts into the header of the report
    2. Copy the Formulas from these cells to Notepad (open the Notepad program if needed)
  3. Create a New variable in your report: on the Data Access Menu, select New Dimension from the New variable drop-down, under the 'Data Objects' subtab.
    Create New variable

  4. 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 needed.
  5. Select Dimension on the Qualification drop-down
  6. Click on 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") )
    • Or for MM/DD/YYYY formatted dates:
=MonthsBetween( {insert start date field here};{insert end date field here})
      Create New variable
    • Click the OK button to create the new variable

Usage:

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.

Caveat:

Business Objects calculates the return value for Monthsbetweeen (n) by:

  1. Determining the number of days in the month of first_date
  2. Adding this number of days n times to first_date until the resulting date is the same as or later than last_date
  3. 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 1 month shorter than expected.




Keywords:"Business Objects" IRF Financial Payroll Variable Projections multiplier   Doc ID:17668
Owner:James I.Group:University of Chicago
Created:2011-03-28 18:00 CSTUpdated:2015-12-21 12:07 CST
Sites:University of Chicago, University of Chicago - Sandbox
Feedback:  3   0