Skip to Content
0

Subtraction in hierarchy

Mar 17, 2017 at 01:48 PM

128

avatar image
Former Member

Hello I need some help with hierarchy help.. The users want to subtract a roll up from a hierarchy, but changing to calculate total as sum breaks when they expand hierarchy.

So they want it like this They want the parent to be child 1 minus child 2. They also don't want to have to display the hierarchy for the subtraction to work.

Parent

CHild 1

Child 2

Child of Child 2

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Gregor Dieckmann
Mar 20, 2017 at 12:42 PM
0

Hi Matt,

you can use the hierarchy attributes for sign reversal and a formula in the query where you multiply the key figure value with the hierarchy attribute value (+1 or -1). You get this value using a formula variable of the corresponding type, cf. documentation.

Regards,

Gregor

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thank you!


From what we are seeing it isn't actually subtracting it is just makes it visibility negative.

So it would look something like this.

Without sign reversal

Parent 500

Child 400

Child 100

Sign reversed

Parent 500

Child 400

Child -100 (reversed here)

0

Hi Matt,

I forgot that formula variables based on hierarchy attributes are calculated after aggregation, you need formula calculation and then aggregation. If you only need this sign reversal for leaves you can use a sign attribute for the master data (+1,-1) and multiply with the attribute values.

Regards,

Gregor

0
Former Member

Thank you! you are correct. The only way to get this to work is exception aggregation!

0