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
Consolidation sum to exclude one element
-
- 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
Last edited by ViRa on Thu Jun 07, 2018 7:30 pm, edited 1 time in total.
-
- MVP
- Posts: 3128
- 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
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
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
-
- 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
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.
-
- 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
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 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.
-
- MVP
- Posts: 3128
- 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
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
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
- Alan Kirk
- Site Admin
- Posts: 6610
- 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
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.ViRa wrote: ↑Thu Jun 07, 2018 7:28 pmThanks 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 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.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 3667
- 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
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.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
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.