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
Count of String Data
- LeeTaylor1979
- Posts: 63
- Joined: Mon Aug 25, 2008 12:53 pm
- OLAP Product: IBM Cognos TM1
- Version: 10.2.2
- Excel Version: 2010
-
- MVP
- Posts: 1817
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Count of String Data
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.
['Pass']=N: IF ( DB(Cub, !dim1, !dim2... 'Status';)@='Pass',1,0);
should do the trick for you.
Declan Rodger
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: Count of String Data
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.
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.
Kamil Arendt
- LeeTaylor1979
- Posts: 63
- Joined: Mon Aug 25, 2008 12:53 pm
- OLAP Product: IBM Cognos TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Count of String Data
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 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 ?
- Attachments
-
- EHS Audit.xlsx
- (186.5 KiB) Downloaded 241 times
- LeeTaylor1979
- Posts: 63
- Joined: Mon Aug 25, 2008 12:53 pm
- OLAP Product: IBM Cognos TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Count of String Data
I am very open to suggestion on changing the Cube itself if you guys think there is a better way.
- qml
- MVP
- Posts: 1094
- Joined: Mon Feb 01, 2010 1:01 pm
- OLAP Product: TM1 / Planning Analytics
- Version: 2.0.9 and all previous
- Excel Version: 2007 - 2016
- Location: London, UK, Europe
Re: Count of String Data
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?
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?
Kamil Arendt