# Qlik Sense App Development

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor II

## Accumulative hierarchy calculation in script

Hi, I have a customer data with hierarchy structure, my challenge is to calculate the revenue of each customer including all his childrens.  Put in a simplified example:

Revenue:
customer_id, revenue
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
10,10
11,11
12,12
13,13
14,14
]
;

Customer:
customer_id, parent_customer_id
1,
2,1
3,1
4,3
5,3
6,2
7,2
8,7
9,7
10,9
11,9
12,5
13,5
14,5
]
;

The result would be:

 customer_id parent_customer_id Sum(revenue) 1 1 2 1 2 3 1 3 4 3 4 5 3 5 6 2 6 7 2 7 8 7 8 9 7 9 10 9 10 11 9 11 12 5 12 13 5 13 14 5 14

What I need is to calculate a new column: Accumulative Revenue, each customer_id has the revenue including itself and all the children.

 customer_id parent_customer_id Sum(revenue) Accumulative Revenu 1 1 105 2 1 2 53 3 1 3 51 4 3 4 4 5 3 5 44 6 2 6 6 7 2 7 45 8 7 8 8 9 7 9 30 10 9 10 10 11 9 11 11 12 5 12 12 13 5 13 13 14 5 14 14

I believe this should be done in script, as I have many other dimension like date, month etc....

Labels (2)

• ### hierarchy

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Accumulative hierarchy calculation in script

Customer:
customer_id, parent_customer_id
1,
2,1
3,1
4,3
5,3
6,2
7,2
8,7
9,7
10,9
11,9
12,5
13,5
14,5
];

Revenue:
customer_id, revenue
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
10,10
11,11
12,12
13,13
14,14
]
;

Hierarchy:
HierarchyBelongsTo(customer_id,parent_customer_id,CID,parent_name,[customer id], PathNam)
Resident Customer;

Left Join(Hierarchy)
parent_customer_id as [parent customer id]
Resident Customer;

Drop Table Customer;

Left Join(Hierarchy)
revenue as [revenue actual]
Resident Revenue;

Create a report like below

Dimension:

[customer id]

[parent customer id]

Measure:

Sum(revenue) -Accumulated Revenue

Sum(distinct [revenue actual])- Revenue

8 Replies
Highlighted

## Re: Accumulative hierarchy calculation in script

What is the logic behind for 53 etc.

 2 1 2 53
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor II

## Re: Accumulative hierarchy calculation in script

sum of revenue of customer 2 and all it's children: 2 + 6+7 +8+9+10+11 = 53

Highlighted
MVP

## Re: Accumulative hierarchy calculation in script

There might be an easier way to do this, but try this

``````FieldMapping:
Mapping
F1, F2
customer_id_new, customer_id
parent_customer_id_new, parent_customer_id
customer_name_new, customer_name
parent_name_new, parent_name
PathName_new, PathName
Depth_new, Depth
];

Customer:
Hierarchy(customer_id, parent_customer_id, customer_name, parent_name, customer_name, PathName, '\', Depth)
parent_customer_id,
customer_id as customer_name
INLINE [
customer_id, parent_customer_id
1
2, 1
3, 1
4, 3
5, 3
6, 2
7, 2
8, 7
9, 7
10, 9
11, 9
12, 5
13, 5
14, 5
];

tmpCustomer:
CrossTable (Customer_Depth, master_parent_id, 6)
parent_customer_id as parent_customer_id_new,
customer_name as customer_name_new,
parent_name as parent_name_new,
PathName as PathName_new,
Depth as Depth_new,
*
Resident Customer;

FinalCustomer:
NoConcatenate
Resident tmpCustomer
Where WildMatch(Customer_Depth, 'customer_name*');

DROP Tables Customer, tmpCustomer;

RENAME Field using FieldMapping;

Revenue:
customer_id, revenue
1, 1
2, 2
3, 3
4, 4
5, 5
6, 6
7, 7
8, 8
9, 9
10, 10
11, 11
12, 12
13, 13
14, 14
];``````

After this is done, create a table with master_parent_id as the dimension and Sum(revenue) as your expression

Highlighted
Partner

## Re: Accumulative hierarchy calculation in script

Why do you want to fix this in the script?

Just define the hierarchy and sum up the values in the front-end.

Highlighted
MVP

## Re: Accumulative hierarchy calculation in script

Customer:
customer_id, parent_customer_id
1,
2,1
3,1
4,3
5,3
6,2
7,2
8,7
9,7
10,9
11,9
12,5
13,5
14,5
];

Revenue:
customer_id, revenue
1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
10,10
11,11
12,12
13,13
14,14
]
;

Hierarchy:
HierarchyBelongsTo(customer_id,parent_customer_id,CID,parent_name,[customer id], PathNam)
Resident Customer;

Left Join(Hierarchy)
parent_customer_id as [parent customer id]
Resident Customer;

Drop Table Customer;

Left Join(Hierarchy)
revenue as [revenue actual]
Resident Revenue;

Create a report like below

Dimension:

[customer id]

[parent customer id]

Measure:

Sum(revenue) -Accumulated Revenue

Sum(distinct [revenue actual])- Revenue

Highlighted
MVP

## Re: Accumulative hierarchy calculation in script

My man is back...

Highlighted
MVP

## Re: Accumulative hierarchy calculation in script

haha. thanks man. let's see how long I sustain

Highlighted
New Contributor II

## Re: Accumulative hierarchy calculation in script

Wow, Amazing! I think both @kush141087 and @sunny_talwar  solution work! Thank you so much!