Handling NA Values When Allocating Data

Sometimes you want to overwrite existing data when allocating values to a target variable and at other times you want to write allocated values to target cells that have an NA basis before the allocation. For example, when you create a new product in your product dimension, then no basis exists for the new product in your budget variable. You want to allocate advertising costs for the entire product line, including the new product.

You can handle NA values using formulas and hierarchical operators in a RELATION (for allocation) statement in the following ways:

• Handling NA data with formulas—One way to handle the NA values is to construct a basis that only describes the desired target cells. This is the preferred method. You can refine your choice of basis values by deriving the basis from a formula. The following statements define a formula that equates the values of the new product to twice the value of an existing product. You could use such a formula as the basis for allocating advertising costs to the new product.

  DEFINE formula_basis FORMULA DECIMAL <product>
  EQ IF product EQ 'NEWPRODUCT' -
     THEN 2 * product.budget(product 'EXISTINGPRODUCT') -
     ELSE product.budget
  
  

• Handling NA data with hierarchical operators—To allocate data to target cells that currently have NA values, use a hierarchical operator in a RELATION (for allocation) statement in the allocation specification. The hierarchical operators use the hierarchy of the dimension rather than existing data as the allocation basis. A danger in using hierarchical operators is the possibility of densely populating your detail level data, which can result in a much larger analytic workspace and require much more time to aggregate the data.

  To continue the example of allocating the advertising cost for the new product, you could use the hierarchical last operator HLAST to specify allocating the cost to the new (and presumably the last) product in the product dimension hierarchy.