Child node to equal the sum of Parent node

[Former Member]

Hi,

Is it possible to make the value of "child" node to equal the sum of "parent" node in BEx hierarchy via WebI (BICS connection)?

See below for example

              

Since we are using BEx Hierarchy (not via traditional UNX), the hierarchy is treated as one object. I haven't work out a way to use IN function or runningsum() to achieve the desire output.

Using Hierarchy.Depth(), Children(), Sibling() or Parent() doesn't seem to work either.

Reason for this changes:      

Need to do additional calculation where the child / total parent (see 3rd column above).

Using above sample

Crumbs and Coatings = 10/10 = 1

Helgas Muffins = 12/30 = 0.4

Have anyone come across this issue or know a workaround to it?

Does it needs to be done on BEx query side, if so, how?

[Former Member]

Hi Ken,

to calculate your child to equal parent field use following formula:

total_parent=sum(measure) in([parent])

where measure = measure object that showing normal aggregation...

Parent = parent attributes like Baking crubs , bakery snacks etc.

Now in final column apply formula as
calculation = [measure]/[total_parent]

Thanks,

SB

[Former Member]

So, you want to know how to get the 0.4 for Helgas Muffins?

=[Normal Aggregation]/Sum([Normal Aggregation]) in ([Parent]) should do the trick

Assumptions:

Normal Aggregation is the name of your object and matches the column name

Parent is the object name for the object containing Baker Snacks and Baking Crumbs