Range and Financial Functions
Range calculation functions in Essbase take a range of members as input. The financial functions execute specialized calculations (for example, interest, depreciation, growth, and discount).
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 call.
Table 3-9 Range and Financial Functions
| 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). |
| 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
rangeList is a list of members from a single dimension only. 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
Examples of XrangeList
XrangeList is a range of members from one or more dimensions, and can help you incorporate time continuum navigation. 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 cube. It is based on a cube 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
More Examples of rangeList and XrangeList
The following table provides more examples of valid values for rangeList or XrangeList.
Table 3-10 Valid Values for rangeList and 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.
Table 3-11 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. |