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 »

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
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

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

Post by Elessar »

Hi,

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

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+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 »

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)
Technical Director
www.infocat.co.uk
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 »

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: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+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 »

Ok, then the rule is the way to go.

['Customer Count']=N:If ( Customer is active=1 , 1 , 0);
Technical Director
www.infocat.co.uk
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

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

Post by Elessar »

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
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Post Reply