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,
Count the number of children and store it as an attribute for the parent
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
- Elessar
- Community Contributor
- Posts: 338
- 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
Hi,
You can use the "ELCOMPN(dimension, element)" function (both in rule or a TI):
https://www.ibm.com/support/knowledgece ... compn.html
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.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
- Steve Rowe
- Site Admin
- Posts: 2415
- 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
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)
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
www.infocat.co.uk
-
- 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
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.
- Steve Rowe
- Site Admin
- Posts: 2415
- 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
Ok, then the rule is the way to go.
['Customer Count']=N:If ( Customer is active=1 , 1 , 0);
['Customer Count']=N:If ( Customer is active=1 , 1 , 0);
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- Elessar
- Community Contributor
- Posts: 338
- 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
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.
TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.