TM1 Forum

Hello all!

Is there a function that returns the count of dimension elements (N-Level specifically) for a given cell value?

For example If I have a 6 dimension sales cube with store, product, period, category, employee, and scenario and I am looking at ALL Stores (Top Consolidation) and the total amount for a given product. I would like to also be able to show the number of stores that actually had sales for that product (see below).

SalesCube:Stores ALL
SalesCube:Period OCT-09
SalesCube:Employee ALL
SalesCube:Category New

Quantity Amount # of Stores
Product A 53 $622.32 43
Product B 27 $322.99 9
Product C 15 $230.64 5

I would there is a Worksheet function to do this but I would also settle for a rule based function.

Any help is greatly appreciated!

~Kevin B.

Cognos TM1 v9.4 MR1 Windows 2003 Server 32-bit 3GB RAM

you should be able to a new member in the production dimension ('No. of Stores').

So you would make a rule:
# This would count all stores that have amount greater than 0.
['No. of Stores'] = N:if (['Amount'] > 0, 1, 3);

Sign() works well here - saves lots of mucky IFs.
SIGN

This is a TM1 rules function, valid in both TM1 rules and TurboIntegrator processes.

SIGN determines if a number is positive, negative, or zero. The function returns 1 if the number is positive, -1 if the number is negative, and 0 if the number is zero.
Syntax

SIGN(number)

Might be worth wrapping an ABS round it if you are likely to have negatives in your data.

I tried adding a 'No of Stores' element to the Product dimension and created a rule: ['No. of Stores'] = N:if (['Amount'] > 0, 1, 3);
This did not work as all products just returned a 0.

Wouldn't I create the element "No. of Stores" in the Scenario Dimension and somehow tell the rule to only reference the Stores Dimension?

SalesCube:Stores ALL SalesCube:Period OCT-09
SalesCube:Employee ALL
SalesCube:Category New

Quantity Amount # of Stores
Product A 53 $622.32 43
Product B 27 $322.99 9
Product C 15 $230.64 5

What am I missing?

~Thanks!
Kevin B

Yes, you can't put it in product because you need to reference the products. (Remember rules are declarative, not procedural.)
Joe Puztai and the Best Practices mob recommended adding a spare 'measure' dimension to every cube when you create it. Very good idea. If you haven't got one you can probably add it to one of the other dims.

absolutely correct. My fault.