TM1 Forum

Hi,

I have a cube with customer dimension which has an hierarchy like

Region ->Agency - > Rep-> Customer


Now the cube has measures based on the customer loaded like recent activity , balances, sales etc .


Users wants to know the count of number of customers for each region and each agency .

Now I plan to store the count as an attribute value on the region and agency level .

How would I go about going this count and storing as an attribute.

Do I have two TI process with one have all regions as source and Iterate them and counts its customers and other Ti with Agency as subset and iterate all agencies and find the count of children .

How to do I get the number of children of a region , meaning the customers which is 3 rd level .

Thanks,

Hi,

You can use the "ELCOMPN(dimension, element)" function (both in rule or a TI):
https://www.ibm.com/support/knowledgece ... compn.html

I wouldn't use an attribute as this stops the measure adding up.

If you create a 2d cube of Customer and a Measure dimension with an element "customer count".

Then either
Just fully expand the customer dimension and spread a 1 into all cells "r|1" if you have customers on rows.
or more dynamically use a (unfed or skipchecked) rule.
['Customer Count']=N:1;

Then the C levels will contain the count of all the N levels beneath them (which ElcompN doesn't do)

Thanks for the suggestions. I cannot spread 1 as I will also have to do some conditional check to see if the customer is active based on another measure.

Ok, then the rule is the way to go.

['Customer Count']=N:If ( Customer is active=1 , 1 , 0);

Note that this works only if all element weights are "1" (which is true in your case). Otherwise you should create a clone dimension with "1" weights for all elements