Consolidation sum to exclude one element

Post Reply
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Consolidation sum to exclude one element

Post by ViRa »

Hi all,

A dimension in the cube shows sum of all its elements at the consolidation level. I am required to sum at the consolidation level for all the elements except 1. Eg. if the dimension has 10 elements and value against each of the element is 1, the sum at consolidation is showing 10. However, I need the sum at consolidation to show value as '9' since the sum should exclude one of the element (lets say el 9). Can you please guide me how to achieve this?

Thanks
Last edited by ViRa on Thu Jun 07, 2018 7:30 pm, edited 1 time in total.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Consolidation sum to exclude one element

Post by Wim Gielis »

You can create the total as a sum of 2 elements, the consolidation of 9 and the 10th leaf level element. Very easy.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Consolidation sum to exclude one element

Post by ViRa »

Please note, there are no other consolidations in the dimension that I could ConsolidateChildren (). Hence clueless how to go about resolving this issue. Appreciate your help.
ViRa
Regular Participant
Posts: 155
Joined: Tue May 14, 2013 1:53 pm
OLAP Product: Cognos BI, TM1
Version: 9.5.2 - 10.1.1
Excel Version: Excel 2003

Re: Consolidation sum to exclude one element

Post by ViRa »

Wim Gielis wrote: Thu Jun 07, 2018 7:25 pm You can create the total as a sum of 2 elements, the consolidation of 9 and the 10th leaf level element. Very easy.
Thanks Wim. I do not want to specify the elements explicitly in the rule since there are chances of additional elements being added in the future. So how much ever elements gets added in the future, the el 9 should always be excluded in the summation. How do I achieve this?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Consolidation sum to exclude one element

Post by Wim Gielis »

ViRa wrote: Thu Jun 07, 2018 7:28 pmthe el 9 should always be excluded in the summation. How do I achieve this?
Why not just looking in the cube at the parent of the 9 elements ?
If you add a 10th element, then the parent is still correct, no ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Consolidation sum to exclude one element

Post by Alan Kirk »

ViRa wrote: Thu Jun 07, 2018 7:28 pm
Wim Gielis wrote: Thu Jun 07, 2018 7:25 pm You can create the total as a sum of 2 elements, the consolidation of 9 and the 10th leaf level element. Very easy.
Thanks Wim. I do not want to specify the elements explicitly in the rule since there are chances of additional elements being added in the future. So how much ever elements gets added in the future, the el 9 should always be excluded in the summation. How do I achieve this?
As Wim alluded to, you don't sum values in rules, you sum them in consolidations. For one thing, consolidations are faster than rules, for another it makes the calculation more transparent. When you add new elements to the dimension, you also add them to the consolidation which omits the element that you don't want to count. If you're doing this by TI, it's just one extra line of code in the metadata tab.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Consolidation sum to exclude one element

Post by lotsaram »

ViRa wrote: Thu Jun 07, 2018 7:19 pm Hi all,

A dimension in the cube shows sum of all its elements at the consolidation level. I am required to sum at the consolidation level for all the elements except 1. Eg. if the dimension has 10 elements and value against each of the element is 1, the sum at consolidation is showing 10. However, I need the sum at consolidation to show value as '9' since the sum should exclude one of the element (lets say el 9). Can you please guide me how to achieve this?

Thanks
From your profile you joined this forum in May 2013. Therefore at the time of posting this question you have been using TM1 for one would assume at least 5 years. I find it therefore astounding that you are asking this question. As others have alluded. The answer is very simple. Either exclude the element from the consolidation or include it in the consolidation but with a weighting of 0.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
Post Reply