Function or Rule to return the count of elements in a DIM

[KevinB]

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

[ajain86]

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);

[David Usherwood]

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.

[KevinB]

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

[David Usherwood]

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.

[ajain86]

absolutely correct. My fault.