Range and Financial Functions

Range functions take a range of members as an argument. Rather than return a single value, these functions calculate a series of values internally based on the range specified.

Financial functions execute specialized financial calculations.

Function Return Value
@ACCUM The sum of values of a specified member across a range
@AVGRANGE The average of values of a specified member across a range
@COMPOUND The compound interest of values of a specified member across a range, calculated at a specified rate
@COMPOUNDGROWTH A series of values that represent the compound growth of the specified member across a range of members, calculated at a specified rate
@CURRMBRRANGE A range of members that is based on the relative position of the member combination Essbase is currently calculating
@DECLINE Depreciation of a member over a specified period, calculated using the declining balance method
@DISCOUNT Discounted values of a specified member, calculated at a specified rate, across a range of values from the time dimension
@GROWTH A series of values that represents the linear growth of the specified value
@INTEREST A series of values that represent the linear growth of a specified member, calculated at a specified rate, across a range of members from the time dimension
@IRR The Internal Rate of Return on a cash flow that is calculated across the time dimension or a specified range of members and must contain at least one investment (negative) and one income (positive). Includes an initial guess of 0.07 (the initial guess cannot be configured).
@IRREX The Internal Rate of Return on a cash flow that is calculated across the time dimension or a specified range of members and must contain at least one investment (negative) and one income (positive). Includes functionality to configure the initial guess and the number of iterations the algorithm can make.
@MAXRANGE The maximum value of a member across a range of members
@MAXSRANGE The maximum value of a member across a range of members, with the ability to skip zero and #MISSING values
@MDSHIFT The next or nth member in a range of members, retaining all other members identical to the current member across multiple dimensions
@MINRANGE The minimum value of a member across a range of members
@MINSRANGE The minimum value of a member across a range of members, with the ability to skip zero and #MISSING values
@NEXT The next or nth member in a range of members
@NEXTS The next or nth member in a range of members, with the option to skip #MISSING, zero, or both values
@NPV The Net Present Value of an investment based on a series of payments and income values
@PTD The period-to-date values of members in the time dimension
@PRIOR A list of the previous or nth previous members in a range of members
@PRIORS A list of the previous or nth previous members in a range of members, with the option to skip #MISSING, zero, or both values
@RANGE A member list that crosses the specified member from one dimension with the specified member range from another dimension
@RANGEFIRSTVAL The first value in a range (with options for how to handle zero and #MISSING).
@RANGELASTVAL The last value in a range (with options for how to handle zero and #MISSING).

@SHIFT

@SHIFTPLUS

@SHIFTMINUS

A list of the next or nth members in a range of members, retaining all other members identical to the current member and in the specified dimension
@SLN Depreciation amounts, across a range period, that an asset in the current period may be depreciated, calculated using the straight-line depreciation method
@SUMRANGE A list of summarized values of all specified members across a range of members
@SYD Depreciation amounts, across a range of periods, of an asset in the current period, calculated using the sum of the year's digits depreciation method
@XRANGE A list of a range of members between specified members at the same level

Range List Parameters

Some range and forecasting functions recognize the optional parameter rangeList or XrangeList as the last parameter. rangeList is a range of members restricted to one dimension; XrangeList is a range of members that can be from one or multiple dimensions. XrangeList helps you incorporate time continuum navigation for the calculation functions you use.

If rangeList or XrangeList are not given, the level 0 (leaf) members from the dimension tagged as Time become the default range. If no dimension is tagged as Time and the last parameter is not given, Essbase reports a syntax error.

Examples of rangeList

The following examples are based on Sample Basic.

@CHILDREN(West) is a rangeList that returns the following list:

California 
Oregon     
Washington 
Utah       
Nevada

@CHILDREN(Product) is a rangeList that returns the following list:

Colas      
Root Beer  
Cream Soda 
Fruit Soda 
Diet Drinks

As you can see from the above examples, rangeList is a list of members from a single dimension only.

Examples of XrangeList

The following examples are based on Sample Basic.

The following example uses simple members to return the range between Jan and Mar:

@XRANGE(Jan:Mar)

and returns the following members:

Jan
Feb
Mar

The following example uses cross dimensional members to return the range between Actual, Jan and Budget, Mar:

@XRANGE (Actual->Jan, Budget->Mar)

and returns the following members:

Actual, Jan
Actual, Feb
Actual, Mar
Actual, Apr
Actual, May
Actual, Jun
Actual, Jul
Actual, Aug
Actual, Sep
Actual, Oct
Actual, Nov
Actual, Dec
Budget, Jan
Budget, Feb
Budget, Mar

The following example is not based on the Sample Basic database. It is based on database that contains a dimension called Year that contains members for each year, from 2001 to 2003. The following formula computes the average sales for all months between Mar of 2000 and Jan of 2001:

SalesAvg= @MOVAVG(Sales, 3, @XRANGE("2001"->Mar, "2003"->Jan));

and returns the following members:

          Colas   New York   Actual              
                     Sales       SalesAvg       
                     =====       ========       
2000  
        Mar            678        678   
        Apr            645        645   
        May            675        666   
        Jun            712        677.3 
        Jul            756        714.3 
        Aug            890        786   
        Sep            924        856.7
        Oct            914        909.3
        Nov            912        916.7
        Dec            723        849.7
2001
        Jan            647        760.7 

As you can see from the above examples, XrangeList is a range of members from one or more dimensions, and can help you incorporate time continuum navigation.

More Examples of rangeList and XrangeList

The following table provides more examples of valid values for rangeList or XrangeList.

Example Description
Mar99 A single member
Mar99, Apr99, May99 A comma-delimited list of members.
Jan99:Dec99

A level range.

A level range includes all members on the same level between and including the members defining the range.

Q1_99::Q4_2000

A generation range.

A generation range includes the members defining the range and all members that are within the range and of the same generation.

Q1_99::Q4_2000, FY98, FY99, FY2000 A generation range and a comma-delimited list
@SIBLINGS(Dept01), Dept65:Dept73, Total_Dept A member set function and one or more range lists

The following table provides examples of valid values for XrangeList.

Example Description
Jan->Actual->Sales, Dec->Actual->Sales A comma-delimited list of members from one or more dimensions.
Actual->Jan, @XRANGE(Actual->December, Budget->Mar); A comma-delimited list and a range.
@XRANGE(Jan->Actual,Dec->Budget); A @XRANGE function.
@CHILDREN("Colas"),@CHILDREN("West") A member set function as part of a range list.

Financial functions never return a value; rather, they internally calculate a series of values based on the range specified and write the results to a range of cells. Thus, you cannot apply any operator directly to the function.