Returns parent-level data based on the shared parent value of the current member being calculated.
@SPARENTVAL (RootMbr [, mbrName])
| RootMbr | Defines a member that is used to search for the nearest occurrence of a parent of a shared member. |
| mbrName | Optional. Any valid single member name or member combination, or a function that returns a single member or member combination, from which the parent values are returned. |
Marketing expenses are captured at the Product Category levels in a product planning application. The Product categories are defined as parents 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 / @SPARENTVAL(Product, Sales)) * @SPARENTVAL(Product, Marketing);
which produces the following result:
Sales Marketing
===== =========
100-10 300 37.5
100-20 200 13.3
100 500 #Missing
200-10 100 12.5
200-30 400 26.7
200 900 #Missing
100-10 300 37.5
200-10 100 12.5
Diet 400 #Missing
100-20 200 13.3
200-30 400 26.7
Caffeine Free 600 #Missing
The Marketing expenses can then be reconsolidated across Products and Markets.
@PARENTVAL
@MDPARENTVAL
@SANCESTVAL
| ©2004 Hyperion Solutions Corporation. All Rights Reserved. http://www.hyperion.com |