topic Re: Multti level hierarchy and aggregation in QlikView

Multti level hierarchy and aggregation

xtrimf — Mon, 03 Jun 2013 10:14:18 GMT

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

RedSky001 — Mon, 03 Jun 2013 13:23:47 GMT

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

xtrimf — Mon, 03 Jun 2013 14:25:04 GMT

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

xtrimf — Wed, 05 Jun 2013 05:23:32 GMT

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

RedSky001 — Wed, 05 Jun 2013 08:44:58 GMT

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

xtrimf — Wed, 05 Jun 2013 10:19:37 GMT

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

RedSky001 — Wed, 05 Jun 2013 10:39:26 GMT

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

xtrimf — Wed, 05 Jun 2013 11:02:28 GMT

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

RedSky001 — Wed, 05 Jun 2013 11:34:36 GMT

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

xtrimf — Wed, 12 Jun 2013 07:34:30 GMT

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

RedSky001 — Wed, 12 Jun 2013 08:59:22 GMT

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:

LOAD

Tree

,SUM(Value) AS GRP_VALUE

Resident Account_Hierarchy

Group BY Tree

Re: Multti level hierarchy and aggregation

xtrimf — Wed, 12 Jun 2013 09:35:57 GMT

I see.

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