Count the number of children and store it as an attribute for the parent

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Count the number of children and store it as an attribute for the parent

Post by Analytics123 » Wed Feb 14, 2018 3:15 pm

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,

User avatar
Elessar
Posts: 136
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010
Location: Russia

Re: Count the number of children and store it as an attribute for the parent

Post by Elessar » Wed Feb 14, 2018 3:20 pm

Hi,

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

User avatar
Steve Rowe
Site Admin
Posts: 1783
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Count the number of children and store it as an attribute for the parent

Post by Steve Rowe » Wed Feb 14, 2018 3:25 pm

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)

Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count the number of children and store it as an attribute for the parent

Post by Analytics123 » Wed Feb 14, 2018 3:35 pm

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.

User avatar
Steve Rowe
Site Admin
Posts: 1783
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Count the number of children and store it as an attribute for the parent

Post by Steve Rowe » Thu Feb 15, 2018 9:36 am

Ok, then the rule is the way to go.

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

User avatar
Elessar
Posts: 136
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010
Location: Russia

Re: Count the number of children and store it as an attribute for the parent

Post by Elessar » Thu Feb 15, 2018 9:46 am

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
Sorry for my English ;)

Post Reply