TM1 rule - Change of measures based on element selec ...

yseen

Hi All,

Can I write a rule in TM1 which enables to show 2 different measure values based on the element selected.

Eg : I have a period dimension which has members 2011, Jan, Feb,.. Dec (All are leaf level members)
I have loaded a particular measure for elements Jan to Dec, lets call it as 'Month Employee count' and only for 2011 element I have loaded a value 'Year employee count'.
Can I write a rule in TM1 for a measure 'Actual employee count' - which shows the value as 'Year employee count' when I select 2011 and for all other selections in period it should show 'Month Employee count'
Something like 'Actual employee count' = 'Year employee count If period = 2011 else 'Month Employee count'

Note : Here Year is not the aggregation or average of all months for this measure. Its basically the unique count of employees. Eg if there are 3 employee data line items for 3 different months then count should be 3(month wise), but when you take year the unique employee count is only 1. Thats why I am trying to load for 2 different measures

Regards
Sree

str8899

Possibly try some variation on

['Actual Employee Count'] = IF( !Period @='2011', 'Year Employee Count', 'Month Employee Count');

sunnypure

Or, using TM1's rule precedence:

Code: ['2011','Actual Employee Count'] = N: ['Year Employee Count'];
['Actual Employee Count'] = N: ['Month Employee Count'];

6235999

If you have month structure along with year information then you may write like this

['Actual Employee Count'] = N: IF( SCAN('2011',!Period) @='2011', 'Year Employee Count', 'Month Employee Count');
Or much faster if you have any unique numeric attributes to periods so that you may put your rule based on that numeric attribute in IF statement:
IF(Attrn('Period',!Period,'Numeric attributename') > 44 , 'Year Employee Count', 'Month Employee Count')

Thanks

黄华民

vinnusea wrote:
['Actual Employee Count'] = N: IF( SCAN('2011',!Period) @='2011', 'Year Employee Count', 'Month Employee Count');

Thanks

This won't compile as SCAN returns an integer as to where (position) the String is found.

恭喜发财1

... that's not the only reason vinnusea's suggestion wouldn't compile!  The suggestion is also trying to assign one of 2 string values to a numeric cell.  What he actually meant was this:
Code: ['Actual Employee Count'] = N:
IF( SCAN('2011', !Period) >= 1,
  ['Year Employee Count'],
  ['Month Employee Count']
);

This would work (assuming a combined year-month dimension).  However in this kind of case my preference would be to go for qml's suggestion of using rule precedence.