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
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
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)
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
Thank you! you are correct. The only way to get this to work is exception aggregation!