Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
xtrimf
Creator
Creator

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?

1 Solution

Accepted Solutions
RedSky001
Partner - Creator III
Partner - Creator III

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.

Untitled2.jpg

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.

Untitled3.jpg

View solution in original post

11 Replies
RedSky001
Partner - Creator III
Partner - Creator III

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...

xtrimf
Creator
Creator
Author

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

xtrimf
Creator
Creator
Author

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?

RedSky001
Partner - Creator III
Partner - Creator III

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.

xtrimf
Creator
Creator
Author

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...

RedSky001
Partner - Creator III
Partner - Creator III

What would you expect to see for account Id 2?

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

Untitled.jpg

xtrimf
Creator
Creator
Author

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)

RedSky001
Partner - Creator III
Partner - Creator III

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.

Untitled2.jpg

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.

Untitled3.jpg

xtrimf
Creator
Creator
Author

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