Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Creator III
Creator III

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.

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
Creator III
Creator III

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

Highlighted
Creator
Creator

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

Highlighted
Creator
Creator

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?

Highlighted
Creator III
Creator III

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.

Highlighted
Creator
Creator

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

Highlighted
Creator III
Creator III

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)

Untitled.jpg

Highlighted
Creator
Creator

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)

Highlighted
Creator III
Creator III

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.

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

Highlighted
Creator
Creator

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