Page 1 of 1
Consolidation sum to exclude one element
Posted: Thu Jun 07, 2018 7:19 pm
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
Re: Consolidation sum to exclude one element
Posted: Thu Jun 07, 2018 7:25 pm
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.
Re: Consolidation sum to exclude one element
Posted: Thu Jun 07, 2018 7:25 pm
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.
Re: Consolidation sum to exclude one element
Posted: Thu Jun 07, 2018 7:28 pm
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?
Re: Consolidation sum to exclude one element
Posted: Thu Jun 07, 2018 9:31 pm
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 ?
Re: Consolidation sum to exclude one element
Posted: Thu Jun 07, 2018 9:40 pm
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.
Re: Consolidation sum to exclude one element
Posted: Fri Jun 08, 2018 6:38 am
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.