Prevent Consolidation Sums for an Element

[ctang]

Hi,

I've done a bunch of googling on this issue without results, so I'm hoping the experts here can help. I have a cube that mainly collects numerical data along with notes about this data. It uses hierarchies to sum the data up for various subtotals. I'd like to add an element to the lowest level dimension with is numerical but does not sum along the consolidations. Is there a rule I can write to prevent this?

Thanks in advance!
Charlotte

[declanr]

When you add an element under a consolidation you apply a weighting to it. Usually this weighting is 1 and if all children of a consolidation have a weighting of 1 then it just sums them up.
For this element you can just give it a 0 weighting, if you are using dimensionelementcomponentadd or similar ti function then just put a 0 at the end in place of a 1 or you can manually apply the weighting in whichever GUI you are using for development.

[paulsimon]

Hi Charlotte

I am not sure what you are asking. If you want an element at the bottom of the hierarchy and you don't want values in this to sum up, then using a zero weight as Declan suggests is the way to go.

However, if when you say the lowest level dimension, you actually mean the measures dimension and you have a measure that you don't want to sum up into the consolidations in all the other dimensions in the cube, then you use a rule like this

skipcheck ;

['the measure'] = C: 0 ;

Replace the measure with whatever the particular measure is called.

Regards

Paul SImon

[ctang]

Thanks Declan and Paul!

this is closest to what I am looking for:

skipcheck ;
['the measure'] = C: 0 ;

But instead of setting them all to zero, I'd like them to be fields I can enter a number into. So in the cube view, they would be white instead of grey. Basically, I have one numerical measure called "Data" that should roll-up into consolidations and another numerical field that I don't want to have roll-up and I want to be able to enter a value in the C level element.

I may just need to use strings for this, since it seems TM1 wants to sum all the numerical measures on consolidations.

Thanks again,
Charlotte

[Emixam]

[...] and another numerical field that I don't want to have roll-up and I want to be able to enter a value in the C level element.

I may just need to use strings for this

I'm not sure what are the requirements or if it's the best way to do it but that's what you should do; change the element type to 'String'. You can also use the NUMBR function if you need to convert that string into a numerical value.

since it seems TM1 wants to sum all the numerical measures on consolidations.

You simply cannot load/input numerical data into a C level element.

I suggest you to take a look at this post

Hope it helps !

[paulsimon]

Hi Charlotte

It really depends on your purposes but my gut feel is that you should avoid using a string measure.

If you want one measure to roll up and the other not to roll up then you can do this

Have one dimension with a consolidation

A
B C


Have a measures dimension crossed with this with two measures

M1 M2

Then you just have a rule which says [ 'A', 'M1' ] = C: 0 ;

This will set the consolidation level A to 0 for M1 while allowing a user to enter a value into M2 and have it consolidate. If A is a consolidation then it will always show as grey, but if the entry is in the measures dimension that will show as white in combination with B and C.

Another a common technique is to create an A_Input base level element that you enter into which consolidates into A. This is typically used for the case where the user doesn't yet have the information to break A down at the B and C level, but knows what A should be. They could enter into just B, but entering into A_Input makes it semantically clearer that they are entering a value for A because they don't know the breakdown below that. It also avoids issues when a dimension re-organisation results in B being moved to a different consolidation.

A
B C A_Input

Another technique to allow entry at a consolidated level is to have another cube where A is the base level so that it can be entered and then use a rule to pull the value across from the more detailed rule based on some condition eg if the value non-zero.

There are lots of approaches to this. A downside of using a string is that it is less efficient in terms of storage and there is no validation to stop someone typing in something that is not numeric. It can also be confusing as to its intention.

Regards

Paul SImon