TM1 Forum

Hi all,
I am new to this forum and I am glad I found this place.

I am wondering what function I can use to sum all children. in the Time dimension I have a consolidated element called 'YTD', children would be months, however the number of children changes every month, for example, in Jan, YTD have one child (Jan), in Feb YTD have 2 children (Jan/Feb), in Mar YTD have 3 children (Jan/Feb/Mar)...

In general YTD is a natural sum, but there is one account called 'Days' which pull from another cube (Jan=31, Feb=28....), however in the rule of 'Days' I don't want to put N: because I don't want the days rollup at Entity dimension, but I do want it rollup at Time dimension. hence I tried to use ConsolidateChildren function as below, but it did not work, YTD showing 0, I trace calculation, it's using the second formula, not the first one, any work around? Thanks.

['Days','YTD']=ConsolidateChildren('Time');

['Days']=DB('c.Planning',!d.Scenario,'Y' | SUBST(!d.Time,1,4),
SUBST(!d.Time,5,2),!d.Org.EM, !d.Account.Operating, !d.Currency, !d.Measure);

Typically you don't need or want a rule to sum all children. The natural consolidation weighting for rollups takes care of this. So all that is needed is properly designed and constructed dimension hierarchies.

Great care needs to be taken with using ConsolidateChildren as this function by design disables the sparse consolidation algorithm so it can have a debilitating effect on performance where the component aggregation takes place over a large dimension.

However from your question the use case of why you need (or think you need) a rule to sum children isn't really clear. It seems you have a measure called "Days" which should always display a count of the number of days in a consolidated time period. (Presumably regardless of any other dimensionality in the cube.) If this is what you are after then the best solution would simply be to reference via a DB() to a 2-dimensional "helper cube" which stores the data for the days-in-period calculation. The helper cube in this case could be very simple, you just need a numeric measure and populate with a value of 1 at the day level and the natural consolidation will take care of the rest.

Note that you can't use the attribute cube directly for this as even numeric attributes are strings in disguise and don't consolidate themselves, but you could have an attribute for this in the d.Time dimension also pulling via DB() rule from the helper cube.

Note that purely IMHO (and I'm sure others as well) the object naming in your model is a little iffy. What exactly is the purpose of prefixing all cubes with "c." and all dimensions with "d."? (other than to irritate end users?) I would eliminate this to make the tool more user-friendly.

hereim wrote:['Days','YTD']=ConsolidateChildren('Time');

The problem with your rule could just be that the dimension is called "d.Time" and not "Time". (still I wouldn't use ConsolidateChildren anyway if your need is what I think it is from your description.)