This section lists all of the Analytic Services calculation functions, grouped by function type.
A Boolean function returns TRUE or FALSE (1 or 0, respectively). Boolean functions are generally used in conjunction with the IF command to provide a conditional test. Because they generate a numeric value, however, Boolean functions can also be used as part of a member formula.
Boolean functions are useful because they can determine which formula to apply based on characteristics of the current member combination. For example, you may want to restrict a calculation to those members in a dimension that contain input data. In this case, you preface the calculation with an IF test that is based on @ISLEV (dimName, 0).
If one of the function parameters is a cross-dimensional member; for example, @@ISMBR (Sales->Budget), all parts of the cross-dimensional member must match all parts of the current cell to return a value of TRUE.
In the following quick-reference table, "the current member" means the member that is currently being calculated by the function. Words in italics, such as member, loosely indicate information you supply to the function. For details, see the individual function topics.
Function | Condition Tested |
---|---|
@ISACCTYPE | Whether the current member has a particular accounts tag. |
@ISANCEST | Whether the current member is an ancestor of member. |
@ISCHILD | Whether the current member is a child of member. |
@ISDESC | Whether the current member is a descendant of member. |
@ISGEN | Whether the current member of dimension is in generation. |
@ISIANCEST | Whether the current member is the same member or an ancestor of member. |
@ISICHILD | Whether the current member is the same member or a child of member. |
@ISIDESC | Whether the current member is the same member or a descendant of member. |
@ISIPARENT | Whether the current member is the same member or the parent of member. |
@ISISIBLING | Whether the current member is the same member or a sibling of member. |
@ISLEV | Whether the current member of dimension is in level. |
@ISMBR | Whether the current member is member, or is found in member list, or is found in a range returned by another function. |
@ISPARENT | Whether the current member is the parent of member. |
@ISSAMEGEN | Whether the current member is in the same generation as member. |
@ISSAMELEV | Whether the current member is in the same level as member. |
@ISSIBLING | Whether the current member is a sibling of member. |
@ISUDA | Whether the current member of dimension has a particular user-defined attribute string. |
Relationship functions look up specific values within the database based on current cell location and a series of parameters. You can use these functions to refer to another value in a data series. Relationship functions have an implicit current member argument; that is, these functions are dependent on the current member's position.
In the following quick-reference table, words in italics loosely represent information you supply to the function. For details, see the individual function topics.
Function | Return Value |
---|---|
@ANCESTVAL | Ancestor values of a specified one-dimensional member combination. |
@ATTRIBUTEBVAL | Associated attribute value from a Boolean attribute dimension. |
@ATTRIBUTESVAL | Associated attribute value from a text attribute dimension. |
@ATTRIBUTEVAL | Associated attribute value from a numeric or date attribute dimension. |
@CURGEN | Generation number of the current member in dimension. |
@CURLEV | Level number of the current member in dimension. |
@GEN | Generation number of member. |
@LEV | Level number of member. |
@MDANCESTVAL | Ancestor values for any number of multidimensional member combinations. |
@MDPARENTVAL | Parent values for any number of multidimensional member combinations. |
@PARENTVAL | Parent values for member in dimension. |
@SANCESTVAL | Ancestor values for shared members at a certain depth under root member. |
@SPARENTVAL | Parent values for shared members under root member. |
@XREF | Values from a different database than the one being calculated. |
These functions perform specific mathematical calculations. Mathematical functions define and return values that are based on selected member expressions. These functions cover many basic statistical functions and return numeric results that are based on supplied member values. Advanced statistical functions are included in the statistical functions category.
In the following quick-reference table, words in italics loosely represent information you supply to the function. For details, see the individual function topics.
Function | Return Value |
---|---|
@ABS | Absolute value of expression. |
@AVG | Average of all values in expList. |
@EXP | e (base of natural logarithms) raised to the power of expression. |
@FACTORIAL | Factorial of expression. |
@INT | Next lowest integer value of expression. |
@LN | e (base of natural logarithms) of expression. |
@LOG | Any base logarithm of expression. |
@LOG10 | Base-10 logarithm of expression. |
@MAX | Maximum value found in cells of expression list. |
@MAXS | Maximum value found in cells of expression list, optionally skipping empty values. |
@MIN | Minimum value found in cells of expression list. |
@MINS | Minimum value found in cells of expression list, optionally skipping empty values. |
@MOD | Modulus of a division operation between two members. |
@POWER | Expression raised to power. |
@REMAINDER | Remainder value of expression. |
@ROUND | Expression rounded to numDigits. |
@SUM | Sum of values found in cells of expression list. |
@TRUNCATE | Expression with fractional part removed, returning an integer. |
@VAR | Variance between two members. |
@VARPER | Percent variance between two members. |
Member set functions return a list of members. This list is based on the member specified and the function used. You can use operators to specify generation and level ranges with member set functions.
When a member set function is called as part of a formula, the list of members is generated before the calculation begins. The list never varies because it is based on the specified member and is independent of the current member.
If a member set function (for example, @CHILDREN or @SIBLINGS) is used to specify the list of members to calculate in a calculation script, Analytic Services bypasses the calculation of any Dynamic Calc or Dynamic Calc and Store members in the resulting list.
Only the @ATTRIBUTE and @WITHATTR functions can use attribute members or members of the Attribute Calculations dimension as parameters in member set functions.
You can use cross-dimension expressions such as ("1998":"2001" -> @Levmbrs (Year, 0)). The cross-dimensional operator is associative (x -> y) -> z=x -> (y -> z), but not commutative because x -> y = y -> x is a set, but the order of elements is different.
Function | Return Value |
---|---|
@ALLANCESTORS | All ancestors of member, including ancestors of shared member. |
@ANCEST | Ancestor at distance from the current member or an explicitly specified member. |
@ANCESTORS | All ancestors of member, or those up to a specified distance. |
@ATTRIBUTE | All base members associated with attribute member name. |
@CHILDREN | Children of member. |
@CURRMBR | Member currently being calculated in the specified dimension. |
@DESCENDANTS | All descendants of member, or those down to a specified distance. |
@GENMBRS | Members of dimension that are at generation. |
@IALLANCESTORS | Member and ancestors of member, including ancestors of shared member. |
@IANCESTORS | Member and all its ancestors, or those up to a specified distance. |
@ICHILDREN | Member and its children. |
@IDESCENDANTS | Member and all its descendants, or those down to a specified distance. |
@ILSIBLINGS | Member and its left siblings. |
@IRSIBLINGS | Member and its right siblings. |
@IRDESCENDANTS | Member and all its descendants, or those down to a specified distance, including descendants of shared member. |
@ISIBLINGS | Member and its siblings. |
@LEVMBRS | Members of dimension that are at level. |
@LIST | A single list compiled from arguments, and can be used for functions requiring an expression list, a member list, or a range list. |
@LSIBLINGS | Left siblings of member. |
@MATCH | Members that match a pattern search performed over a generation, a level, or a member and its descendants. |
@MEMBER | Member with name string. |
@MERGE | Merged list from two lists. |
@PARENT | Parent of the current member being calculated in dimension, optionally crossed with another member. |
@RANGE | Member list that crosses a member from one dimension with a range from another dimension. |
@RDESCENDANTS | All descendants of member, or those down to a specified distance, including descendants of shared member. |
@RELATIVE | All members that are at distance from member. |
@REMOVE | List1, with anything that is also in list2 removed. |
@RSIBLINGS | Right siblings of member. |
@SIBLINGS | Siblings of member. |
@UDA | Members of dimension that have UDA. |
@WITHATTR | Base members from dimension that are associated with an attribute meeting a condition. |
@XRANGE | Range of members between (and inclusive of) two members at the same level. |
Generation and Level Range Operators for Member Set Functions
The operators : and :: can be used with member set functions, which return a list of members. The : operator returns level-based ranges and the :: operator returns generation-based ranges. For example, Jan:Dec and Jan::Dec both return all members between and inclusive of Jan and Dec.
The difference is that Jan:Dec returns all members at the same level and Jan::Dec returns all members at the same generation.
For example, if we have the outline:
Q1 - Jan Feb Mar Q2 - Apr May Jun Q3 Q4 - Oct Nov Dec
The function @MOVAVG(Sales, 3, Jan:Dec) computes @MOVAVG(Sales, 3, Jan, Feb, Mar, Apr, May, Jun, Q3, Oct, Nov, Dec).
The function @MOVAVG(Sales, 3, Jan::Dec) computes @MOVAVG(Sales, 3, Jan, Feb, Mar, Apr, May, Jun, Oct, Nov, Dec).
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 Analytic Services 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 calculated across the time dimension or a specified range of members |
@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 |
@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 |
Some range and forecasting functions recognize the optional parameter rangeList or XrangeList as the last parameter. rangeList is a range of members from one dimension; XrangeList is a range of members from one or more dimensions.
If rangeList or XrangeList is 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, Analytic Services reports a syntax error.
Examples of valid values for rangeList or XrangeList are:
Mar99 | A single member |
Mar99, Apr99, May99 | A comma-delimited list of members. |
Jan99:Dec99 |
A level 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 |
Examples of valid values for XrangeList are:
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.
These functions allocate values that are input at the parent level. The values are allocated across child members in one or more dimensions, based on specified criteria. These functions consolidate the common tasks that are required to perform allocations in Analytic Services.
Function | Allocation Type |
---|---|
@ALLOCATE | Allocates values to lower-level members in one level. |
@MDALLOCATE | Allocates values to lower-level members in multiple dimensions. |
Forecasting functions manipulate data for the purpose of smoothing, interpolating, or calculating future values. Forecasting functions are often used in planning, analysis, and modeling applications. Some forecasting functions recognize the optional parameter rangeList or XrangeList.
Function | Data Manipulation |
---|---|
@MOVAVG | Applies a moving average to a data set, replacing each term in the list with a trailing average. This function modifies the data set for smoothing purposes. |
@MOVMAX | Applies a moving maximum to a data set, replacing each term in the list with a trailing maximum. This function modifies the data set for smoothing purposes. |
@MOVMED | Applies a moving median to a data set, replacing each term in the list with a trailing median. This function modifies the data set for smoothing purposes. |
@MOVMIN | Applies a moving minimum to a data set, replacing each term in the list with a trailing minimum. This function modifies the data set for smoothing purposes. |
@MOVSUM | Applies a moving sum to a data set. This function modifies the data set for smoothing purposes. |
@MOVSUMX | Applies a moving sum to a data set, enabling specification of values for trailing members. This function modifies the data set for smoothing purposes. |
@SPLINE | Applies a smoothing spline to a set of data points. A spline is a mathematical curve that is used to smooth or interpolate data. |
@TREND | Calculates future values, basing the calculation on curve-fitting to historical values |
Statistical functions calculate advanced statistical values, such as correlation or variance. These functions are often used in sales and marketing applications.
Function | Return Value |
---|---|
@CORRELATION | The correlation coefficient between two parallel data sets |
@COUNT | The number of data values in the specified data set |
@MEDIAN | The median (middle value) of the specified data set |
@MODE | The mode (the most frequently occurring value) in the specified data set |
@RANK | The rank (position in the sorted data set) of the specified members or the specified value among the values in the specified data set. |
@STDEV | The standard deviation of the specified data set |
@STDEVP | The standard deviation of the specified data set, calculated over the entire population |
@STDEVRANGE | The standard deviation of all values of the specified member across the specified data set. The specified mbrName is crossed with a range list to obtain the sample across which the standard deviation is calculated. |
@VARIANCE | The statistical variance of the specified data set (expList), based upon a sample of a population |
@VARIANCEP | The statistical variance of the specified data set (expList), based upon the entire population |
The date function converts date strings to numbers that can be used in calculation formulas.
@TODATE |
Analytic Services includes two types of miscellaneous functions. Using @CALCMODE, you can specifies whether a formula is calculated in cell mode or block mode and whether a formula is calculated bottom-up or top-down. @CONCATENATE and @SUBSTRING enable manipulation of character strings.
@CALCMODE @CONCATENATE @SUBSTRING @NAME |
This custom-defined group is a category of functions that you develop for calculation operations that are not enabled by the built-in Analytic Services functions. Custom-defined functions are written in the Java programming language and registered on the server. The Analytic Services calculator framework calls custom-defined functions as external functions. For more details, see create macro and create function in MaxL.
©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |