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 » 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
Last edited by ViRa on Thu Jun 07, 2018 7:30 pm, edited 1 time in total.

Wim Gielis
MVP
Posts: 1846
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Consolidation sum to exclude one element

Post by Wim Gielis » 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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 » Thu Jun 07, 2018 7:25 pm

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 » 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?

Wim Gielis
MVP
Posts: 1846
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Consolidation sum to exclude one element

Post by Wim Gielis » Thu Jun 07, 2018 9:31 pm

ViRa wrote:
Thu Jun 07, 2018 7:28 pm
the 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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

User avatar
Alan Kirk
Site Admin
Posts: 5839
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Consolidation sum to exclude one element

Post by Alan Kirk » Thu Jun 07, 2018 9:40 pm

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: 3148
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 » Fri Jun 08, 2018 6:38 am

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