11 Replies Latest reply: Jun 12, 2013 5:35 AM by Eldad Tsipris

# Multti level hierarchy and aggregation

Hi,

I have Table with the following information:

Accounts:

AccountID , Name , ParentID , Weight

1     ,a     ,4     ,-1

2     ,b     ,4     ,1

3     ,c     ,4     ,1

4     ,d     ,5     ,1

5     ,e     ,5     ,

6     ,f     ,2     ,-1

7,    ,g    ,2     ,1

Data:

Account, Value

1,     10

1,     5

2,     20

3,     30

4,     40

6,     60

7,     70

The "Weight" field says in which way the child should sum up to its father.

I tried every combination of hierarchy/ belongsto but I could not get the correct result when using "Weight".

The correct result should be

Account, Total

1     10+5 = 15

2     20 + (-60+70) = 30

3     30

4     40+(-15+30+30) =85

5     85

6     60

7     70

In short - while summing up each account, it should concider its childrens and grand childrens (etc...)  weight.

I have attached a QV screenshot of what I am trying to achieve

is it possible to do it it in QV?

• ###### Re: Multti level hierarchy and aggregation

See the attached file.

It's not 100% as my counts for account 4 & 5 are both 85 not 90.

4     40+(-10+30+30) =90

5     90

I should also say, I don't understand how you got to these figures with the data you've provided.  So can you explain where -10+30+30 comes from...

• ###### Re: Multti level hierarchy and aggregation

You are correct - I forgot the 5 so it is 85. I will fix it in the original post.

I'll check your file and get back to you

[

-15 from account 1

30 from account 2

30 from account 3

40 from account 4

] = 85

thank you

• ###### Re: Multti level hierarchy and aggregation

Hi Mike,

thank you again for your solution.

What change need to be made in order to see the accounts in a pivot table?

I'm trying to show the "dril down" of each account:

Level1 (+), Level2 , Level 3 (etc...), Value

is it possible?

• ###### Re: Multti level hierarchy and aggregation

You would just need to change the chart type from a straight table to a pivot table and add the Account name dimension after the Tree (Account id) dimension.

Can you mark my orginal post as correct if it helped.

• ###### Re: Multti level hierarchy and aggregation

Hi,

I did what you suggested - the problem is that I need it to open to N levels.

what you suggest is only the first level (name) after the tree...

the heirerchy that i'm working on right now has atleast 9 levels...

• ###### Re: Multti level hierarchy and aggregation

What would you expect to see for account Id 2?

This is what is currently shows (all it's ancestor records)

• ###### Re: Multti level hierarchy and aggregation

1 - I need to show the real value for "f" and only when summing it up it will be -60

I think this is my main problem.

It is a P&L report and they wish to see all the accounts as they appear and when grouped, it should use the weight. Sp for accountID 2 children  it should show the "real" value of that branch b=20, f=60,g=70

but when showing whithout its children it should show the calculated sum using the weight (same goes for grandchilds and grand-grandchilds etc...)

2 - for account 2 you are correct, but for acount 5 I would expect  5 -> 4 -> 1, 2(->6,7), 3 (tree+ 3 levels)

• ###### Re: Multti level hierarchy and aggregation

Well you could use an expression like this:

if(Dimensionality()=1,sum(Value),sum(OrginalValue))

So when you expand the dimension you see the orginal vlaue.

I'm still nto sure how you want the represent the data.  Using account five as an example, you can see all it's ancestors.

You can use the hierarchy keyword to create an expanded nodes table (see path ) but I don't see an easy way to incoperate this.

• ###### Re: Multti level hierarchy and aggregation

Hi Mark,

I like the technique you used.

How can I modify it that for every combination I will have the value on that line aready?

for example for account id 5 - it will have its sum up of its children already calculated in the script...

tnx

• ###### Re: Multti level hierarchy and aggregation

So you want 85 to be preloaded in the script for account 5?

To do this just create a new table and group by Tree..

GROUPED_VALUES:

Tree

,SUM(Value) AS GRP_VALUE

Resident Account_Hierarchy

Group BY Tree

• ###### Re: Multti level hierarchy and aggregation

I see.

I have 14 levels...so I need to do 14 group bys'?