@SANCESTVAL

The @SANCESTVAL() function returns ancestor-level data based on the shared ancestor value of the current member being calculated.

Syntax

@SANCESTVAL (rootMbr,genLevNum [, mbrName])

rootMbr Defines a member that is used to search for the nearest occurrence of an ancestor of a shared member.

genLevNum Integer value that defines the absolute generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference.

mbrName Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, for which the ancestor values are to be returned.

Notes

You cannot use the @SANCESTVAL function in a FIX statement.

Example

Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as ancestors that contain shared members as children. The Marketing Expense data must be allocated down to each Product code based on Sales contribution.

The following Product hierarchy is defined:

Product
   100
        100-10
        100-20
   200
        200-10
        200-20
   Diet ~
        100-10 SHARED
        200-10 SHARED
   Caffeine Free ~
        100-20 SHARED
        200-20 SHARED

                 Sales   Marketing
                 =====   =========
				 
100-10            300       0
100-20            200       0
100               500       0
200-10            100       0
200-30            400       0
200               900       0
100-10            300       0
200-10            100       0
Diet              400      50
100-20            200       0
200-30            400       0 
Caffeine Free     600      40

The Marketing Expense value is allocated down to each Product code with the following formula:

Marketing = (Sales / @SANCESTVAL(Product, 2, Sales)) * @SANCESTVAL(Product, 2, Marketing);

Which produces the following result:

                 Sales    Marketing
                 =====    =========

100-10           300       37.5
100-20           200       13.3
100              500       #MI
200-10           100       12.5
200-30           400       26.7
200              900       #MI
100-10           300       37.5
200-10           100       12.5
Diet             400       50
100-20           200       13.3
200-30           400       26.7
Caffeine Free    600       40

The Marketing expenses can then be reconsolidated across Products and Markets.

See Also

@ANCESTVAL
@MDANCESTVAL
@SPARENTVAL

rootMbr	Defines a member that is used to search for the nearest occurrence of an ancestor of a shared member.
genLevNum	Integer value that defines the absolute generation or level number from which the ancestor values are to be returned. A positive integer defines a generation reference. A negative number or value of 0 defines a level reference.
mbrName	Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, for which the ancestor values are to be returned.