Rule to sum all elements except the current?

[Aerouge]

Hey guys,
I need a calculation in my rules that sums up all elements except the current one.

Something like this:

['Element 3','Value']:N ['Element 1'] + ['Element 2'] + ['Element 4']

The thing is... the Dimension changes constantly so I cant write a fixed statement and need some kind of logic.

Since we cant use Dynamic Subsets nor Loops in Rules I am somewhat stumped.

I could create a consolidation in the dimensionscript but would actually like to avoid creating new consolidations all the time. But this seems like the only way to solve this "dynamical"? Or am I missing a function / method to solve this?

In short ... is there a more elegant way to achieve this except creating a new c-element every time the dimension updates?

[burnstripe]

Assuming that element 3 value can just be a calculation and doesn't need to be fed then you could maybe do this

['dim1':'dim1':'Element 3','Value']:N ['Dim 1':'hier','all elements'] - ['dim 1':'hier':'Element 3'];

Where you're referring to an alternative hierarchy to pull back all elements and subtract the existing one

If it needs to be fed then having a process to update the consolidations is the best way

[MarenC]

Hi,

You didn't make it clear to me what current element means. If it means what is in the left side of the rule, then you could use the exclamation mark notation to specify the contextual element. But I guess that cant be what you mean, so what do you mean by current element?

I could create a consolidation in the dimensionscript but would actually like to avoid creating new consolidations all the time.

Assuming you made the code dynamic then you wouldn't have to create consolidations all the time, the TI would and it won't complain! This seems like a perfectly sensible and elegant option to me. You might be able to do something with attributes as an alternative approach.

Maren

[Aerouge]

Hi,

You didn't make it clear to me what current element means. If it means what is in the left side of the rule, then you could use the exclamation mark notation to specify the contextual element. But I guess that cant be what you mean, so what do you mean by current element?

I could create a consolidation in the dimensionscript but would actually like to avoid creating new consolidations all the time.

Assuming you made the code dynamic then you wouldn't have to create consolidations all the time, the TI would and it won't complain! This seems like a perfectly sensible and elegant option to me. You might be able to do something with attributes as an alternative approach.

Maren

Hy MarenC and thanks for the reply,

yes the "current" element is the one the rule is supposed to calculate.

In my example it is indeed the one of the left side of the rule.
And I know that I could reference it on the right side of the rule with an exclamation mark, but how would that actually help in the given situation?

As for why I wouldnt like a C-Element .. I dislike the use of purely technical elements in my hierarchies (from a design and administrative point of view). Having interlocking functions always introduces possible fail-points as well. In the current case the rule would calculate nonsense if the technical C-Element was somehow wrong, so I would need to check the rule AND the C-Element (and according TI) if I would need to debug it. So if I could keep the code purely to the rules part I'd prefer that ... still I dont know if this is possible ... hence this post.

[Aerouge]

Assuming that element 3 value can just be a calculation and doesn't need to be fed then you could maybe do this

['dim1':'dim1':'Element 3','Value']:N ['Dim 1':'hier','all elements'] - ['dim 1':'hier':'Element 3'];

Where you're referring to an alternative hierarchy to pull back all elements and subtract the existing one

If it needs to be fed then having a process to update the consolidations is the best way

Hy burnstrip, thanks for the reply!

Wouldnt your rule result in a circular reference? No matter if fed or not?

But anyway ... yes the rule needs to be fed ... we're writing it because we want to do something with the sum ( imagine the question given beeing a part of a longer and more complex rule and I'm just asking for the part I'm stubbing my toes on )

[MarenC]

Hi,

And I know that I could reference it on the right side of the rule with an exclamation mark, but how would that actually help in the given situation?

The given situation you mentioned in your first post was to minus this value from the calculation, therefore because it is specifically referenced on the left side, it can easily minus on the right side.

Or is the problem, how to sum up all the elements, which would suggest you have no consolidation at all, which is unusual?

Maren

[Aerouge]

Hi,

And I know that I could reference it on the right side of the rule with an exclamation mark, but how would that actually help in the given situation?

The given situation you mentioned in your first post was to minus this value from the calculation, therefore because it is specifically referenced on the left side, it can easily minus on the right side.

Or is the problem, how to sum up all the elements, which would suggest you have no consolidation at all, which is unusual?

Maren

Nope the problem would have to do with a circular referencation.

If I'd write:
["Element 3"] = N: ["All Elements"] - [!Element]

TM1 would be kinda displeased

[declanr]

Nope the problem would have to do with a circular referencation.

If I'd write:
["Element 3"] = N: ["All Elements"] - [!Element]

TM1 would be kinda displeased

What is the cube structure you are working with and what are you trying to get a sum of?

As long as you have a measures dimension you could get around it with a second measure e.g.

Code: Select all

['HierarchyDim':'Element3', 'MeasureDim':'Measure2']=N:
	['HierarchyDim':'All Elements', 'MeasureDim':'Measure1' ] - ['MeasureDim':'Measure1'];

[MarenC]

Nope the problem would have to do with a circular referencation.

Oh right, so its all the same measure. I was assuming your example was not the real rule but just for representation. Another measure might be another alternative then.

Edit - Declan beat me to the punch!

Maren

[Aerouge]

Nope the problem would have to do with a circular referencation.

If I'd write:
["Element 3"] = N: ["All Elements"] - [!Element]

TM1 would be kinda displeased

What is the cube structure you are working with and what are you trying to get a sum of?

As long as you have a measures dimension you could get around it with a second measure e.g.

Code: Select all

['HierarchyDim':'Element3', 'MeasureDim':'Measure2']=N:
	['HierarchyDim':'All Elements', 'MeasureDim':'Measure1' ] - ['MeasureDim':'Measure1'];

Hy Declan,
thats a good idea ... but that'd be too simple to work

To keep the example simple I am already omitting most stuff. But what I am wondering if it's possible would be something like this:

Code: Select all

['HierarchyDim':'Element3', 'MeasureDim':'Measure1']=N:
	['HierarchyDim':'All Elements', 'MeasureDim':'Measure1' ] - ['MeasureDim':'Measure1'];

Otherwise having a technical C-Element or an aritifical N-Element in another Dimension would be a solution... but I'd love to solve this purely within rules ... without bloating dimensions with technical elements.

[burnstripe]

Reflecting, consolidations is the only way to go. From a performance perspective you're going to need to use consolidations and should you attempt to refer to a consolidation with all leaves it's going to cause a circular reference, alternate hierarchy or not.

Other options to using consolidations would also require more maintenance. To keep the main hierarchy clean I'd create an alternative hierarchy which houses these all but current element consolidations.

[Aerouge]

Reflecting, consolidations is the only way to go. From a performance perspective you're going to need to use consolidations and should you attempt to refer to a consolidation with all leaves it's going to cause a circular reference, alternate hierarchy or not.

Other options to using consolidations would also require more maintenance. To keep the main hierarchy clean I'd create an alternative hierarchy which houses these all but current element consolidations.

Hy burnstripe,
thanks! I was afraid that'd be the answer ... if only we could use MDX in the rules (a simple EXCEPT would solve the issue in a one-liner) or any kind of programming (a simple loop with an if ) ... but allas ... still thank you all for thinking this through with me