Rule to sum all elements except the current?

Post Reply
Aerouge
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Rule to sum all elements except the current?

Post by 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
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Rule to sum all elements except the current?

Post by 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
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Rule to sum all elements except the current?

Post by 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
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: Rule to sum all elements except the current?

Post by Aerouge »

MarenC wrote: Tue May 02, 2023 2:07 pm 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.
Last edited by Aerouge on Tue May 02, 2023 2:34 pm, edited 1 time in total.
Aerouge
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: Rule to sum all elements except the current?

Post by Aerouge »

burnstripe wrote: Tue May 02, 2023 12:55 pm 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 :-D )
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Rule to sum all elements except the current?

Post by 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
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: Rule to sum all elements except the current?

Post by Aerouge »

MarenC wrote: Tue May 02, 2023 2:52 pm 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 :-D
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Rule to sum all elements except the current?

Post by declanr »

Aerouge wrote: Tue May 02, 2023 3:06 pm 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 :-D
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'];
Declan Rodger
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Rule to sum all elements except the current?

Post by 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
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: Rule to sum all elements except the current?

Post by Aerouge »

declanr wrote: Tue May 02, 2023 3:12 pm
Aerouge wrote: Tue May 02, 2023 3:06 pm 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 :-D
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 :-D

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
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Rule to sum all elements except the current?

Post by 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
Posts: 18
Joined: Wed Jan 08, 2014 1:09 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 365

Re: Rule to sum all elements except the current?

Post by Aerouge »

burnstripe wrote: Tue May 02, 2023 10:05 pm 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 :-D
Post Reply