TM1 rule - Change of measures based on element selection

[sreesuku]

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

[Christopher Kernahan]

Possibly try some variation on

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

[qml]

Or, using TM1's rule precedence:

Code: Select all

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

[vinnusea]

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

[Gregor Koch]

['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.

[lotsaram]

... 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: Select all

['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.