TM1 Forum

Hi All,

Is there a TM1 rule equivalent for Countif ?

I have a cube that hold data for audits, so my string values are 'Pass', 'Fail' or in some cases 'N/A'

I can obviously snapshot this to excel and write formulas but wondering if its possible to write a rule that will allow me to count the Pass, Fails and N/A then work out a percentage.

Any help would be appreciated.

Thanks

Lee

Just add an extra measure (numeric) for each status.

['Pass']=N: IF ( DB(Cub, !dim1, !dim2... 'Status';)@='Pass',1,0);

should do the trick for you.

To me the obvious solution would be to create new calculated numeric measures "Count Pass", "Count Fail" and "Count NA". The rules for them would be really simple, along the lines of the following pseudocode:

IF StringMeasure = "Pass"/"Fail"/"N/A" THEN 1, ELSE 0

Your numeric measures are then consolidated in the normal way across your dimension hierarchies, so you can see the statistics immediately on any level you want.

EDIT: declanr beat me to it.

Thanks for your replies,

I have attached the cube and the rule I have put in place.

Obviously I need to put in some feeders now,

Will I need a feeder for each individual measures ?

I am very open to suggestion on changing the Cube itself if you guys think there is a better way.

You just need to feed from the string measures to the numeric "Count" measures.

One issue though is that you have created your rules for all combinations of dimension elements (on the LHS), but they are looking at a specific combination on the RHS, so you would need to feed from one to many, which is less than ideal for a few reasons.

So my first question would be - do the rules need to look/work like that?