on 04-09-2010 9:27 PM
How to find the child count of a dimension member and put the results in an excell column?
I have entities in one column and in the second column i need to have the children of respective member.
Hi Kiran Kumar,
I guess if i understand correctly. You want to find Average based on number of children instead of SUM.
In BPC always into hierachy you will have the parenth like sum of children.
In your case you can use dimension formula wher you don't need to addup all children numbers into parent but into property formula from your dimension you will specify that this member will be calculate average.
You have also MDX formula where you can specify that it is Average of all children.
Anothher solution will be to add a custom measure
(You have an HTG published into BPX/SDN regarding custom measure) to be calculate always like average of children.
I hope this will help you.
Rajesh Muppala.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
What wandi said would work, but I would use COUNTIFS
- add a parent column (assuming you already have a property that mimics PARENTH1)
- your expansion will have to expand on self,all
- Your sheet will look like this
Col A - Entity ID
Col B - Parent property
Col C - CountIF
A B C
1 E1 2
2 E2 E1 =COUNTIF(A2,$B$1:$B$9)
3 E3 E1 0
4 E4 5
5 E5 E4 0
6 E6 E4 0
7 E7 E4 0
8 E8 E4 0
9 E9 E4 0
You can do this on a hidden worksheet and do a vlookup on the sheet you need the number in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear kiran kumar thamatam,
I have an idea for you case. If you had simple hierarchy for your member, let say parent and child. Please you do some tricks from me as following:
1. You could provide some columns in Ms.Excel likes: Account (column A), Parent (column B)
2. Please you use Ms.Excel function SUMIF for calculate the number of members and put it in somewhere cell. The function SUMIF could have some filter criteria for count the number of members, You could reference column B for SUMIF filter criteria.
I apologize for could not provide more information SUMIF format because I really didn't know your dimension hierarchy. Please you refer the Ms.Excel helps for more information how to use SUMIF.
Kind Regards,
Wandi Sutandi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks for the reply
my requirement is
i have the entity members in column A and i need to find no of children(child count) of each of the member in column A and display it in the column B
for example
Column A Column B
member no of childern
entity1 4
entity2 6
entity3 5
i really appreciate if u have an answer
Edited by: kiran kumar thamatam on Apr 11, 2010 9:52 PM
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.