rulescognos-tm1

Equvialent of countif in excel within a tm1 rule?

I want to count cells that include 'EVET' string keyword via below's picture

enter image description here

and paste number of each month into each month, respectively to below's picture

enter image description here

enter image description here

Solution

  • You will need to add an additional (numeric) measure into the v_measure dimension in the "v_planning 02" cube. For the example, I will call it "EVET Count"

    You then add a rule in the "v_planning 02" cube as below:

    ['v_measure':'evet count']=N: If ( DB ( 'v_planning 02', !v_months, !v_row, 'Achieved' ) @= 'EVET', 1, 0 );

    You will want to have skipcheck enabled in this cube and then feed to the "EVET Count" measure (from somewhere sensible) so that it consolidates up correctly.

    Because v_row does not exist in your "v_key performance indicator" cube; you need to sum up the count in the "v_planning 02" cube instead. To do this you will need to have a Consolidated element in the v_row dimension, I will assume you have one called "Total". In the "v_key performance indicator" cube your rule would then be:

    ['v_revenue':'Total Achieved']=N: DB ( 'v_planning 02', !v_months, 'Total', 'EVET Count');