Page 1 of 1
Consolidated rule question
Posted: Fri Apr 20, 2012 12:20 pm
by Stéphane
Hi,
I'm trying tu build a simple rule doing something like ['measure']=C:min(1,['measure'});
(if at least one of my child value is 1, my value is 1, else 0)
it does not work, i got 'N/A' for consolidated levels (I assume it is because ['measure'] is defined by ['measure'] which does not please tm1).
I can do what i want if i create ['measure2']=C:min(1,['measure']);
but I do no want to create a specific measure for it.
Any way I can do what I want without create another measure ?
Thx,
Stéphane
Re: Consolidated rule question
Posted: Fri Apr 20, 2012 12:43 pm
by tomok
In TM1 rules syntax, what you have created results in a circular reference and will not work. The only way I know of, without creating something really convoluted, is the use the second measure.
Re: Consolidated rule question
Posted: Fri Apr 20, 2012 2:23 pm
by rozef
Hi Stephane,
you should try with consolidate children function with a condition to do that:
Code: Select all
['measure']= C:IF( ConsolidateChildren('<<measure_dimension>>') > 0, 1, 0 );
Re: Consolidated rule question
Posted: Fri Apr 20, 2012 3:03 pm
by lotsaram
rozef wrote:Hi Stephane,
you should try with consolidate children function with a condition to do that:
Code: Select all
['measure']= C:IF( ConsolidateChildren('<<measure_dimension>>') > 0, 1, 0 );
Actually as per Stephane's example it would be
Code: Select all
['measure']= C:IF( ConsolidateChildren('<<measure_dimension>>') < 1, ConsolidateChildren('<<measure_dimension>>'), 1 );
This is a good idea in that it
might avoid an explicit circular reference (but I'm not 100% sure on this have you tested?), however it's a bad idea in terms of performance and ConsolidateChildren should be avoided wherever possible. My preference would for sure be the same as Tomok's and to use a duplicate measure (which can always be made invisible to users with element security).
Re: Consolidated rule question
Posted: Fri Apr 20, 2012 3:24 pm
by rozef
lotsaram wrote:Actually as per Stephane's example it would beCode: Select all['measure']= C:IF( ConsolidateChildren('<<measure_dimension>>') < 1, ConsolidateChildren('<<measure_dimension>>'), 1 );
I'm not sure of that...
lotsaram wrote:but I'm not 100% sure on this have you tested?
Sorry I havn't. I was just giving an idea.
rozef wrote:it's a bad idea in terms of performance and ConsolidateChildren should be avoided wherever possible
Personnaly, I never had any performance issue with this function, altough I used it for an algorithm of notation with about 1k society.
Re: Consolidated rule question
Posted: Sun Apr 22, 2012 7:46 pm
by Duncan P
I kind of get the feeling here that the leaf values are guaranteed to be either 1 or 0 and this is trying to do a consolidated logical OR.
If that is the case then the
ConsolidatedMax should do it fine. If your measure can take other values at the leaf level and you really are trying to identify if any of them are 1 then I fear you will need another measure, e.g.
Code: Select all
['MeasureIsOneSomewhere'] = N: If ( 1 = ['Measure'], 1, 0 ); C: ConsolidatedMax( 0, 'cubename', !dim1, !dim2, ..., !measures );
does not suffer from the performance problems that affect
ConsolidateChildren because it works directly from the leaf descendents and not the direct children and so it can be sensitive to feeders.