Home > Working with Planning Projects > Building an Application > Building Application Models > Building A Cube Model > Measure Settings > Cross Referencing Measures > Cross Referencing to the Parent of the node

Cross Referencing to the Parent of the node
Last Updated 3/1/2012 6:22 PM

In some cases, we may want to do cross reference to the value of the parent of the node, either from the same model, or from another model.

EXAMPLE 1 :

Sales model has the following Brand Dimension hierarchy

Total
    Brand A
        Product A1
        Product A2
        Product A3
    Brand B
        Product B1
        Product B2
        Product B3
    Brand C
        Product C1
        Product C2
        Product C3


Sample case is they want to calculate the Sales Ratio for each product against the Sales of each Brand (instead against Total)

    Say each product has the following Sales figure

    Product A1 = 10000        Product B1 = 15000        Product C1 = 12500
    Product A2 = 12500        Product B2 = 15000        Product C2 = 17500
    Product A3 = 17500        Product B3 = 20000        Product C3 = 15000

    Brand A = 40000            Brand B = 50000              Brand C = 45000

    Total = 135000

So the expected sales ratio for each product (against the Total for each Brand, is)

    Product A1 = 10k/40k = 25%
    Product A2 = 12.5k/40k = 31.25%
    Product A3 = 17.5k/40k = 43.75%

    ... and so on for other product

To achieve this result, we can't define each brand name in the calculation, as we want to calculate each product against its Brand.

So for this example, we can use the parent function

Let's assume that Sales model has the following dimension :

dimension1 = Version
dimension2 = Branch
dimension3 = Brand

So the Sales Ratio formula will be : 'Sales'/parent3.'Sales'


The similar function can be used to do cross referencing to another cube model.

EXAMPLE 2 :

we have a cube model for COS Assumption for each Brand (related to the previous Sales model example) :

Model COS Assumption :

dimension1 = Version
dimension2 = Brand (only by Brand level)

Measure = COS %

and the Sales model has the following dimension

dimension1 = Version
dimension2 = Branch
dimension3 = Brand

To calculate the Cost of Sales for each product will be = COS_Assumption.parent3.'COS %' * 'Sales'