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: 
yonghanfi
Contributor II
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:
LOAD * Inline [
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:
LOAD * 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
]
;

The result would be: 

customer_idparent_customer_idSum(revenue)
1 1
212
313
434
535
626
727
878
979
10910
11911
12512
13513
14514

 

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

customer_idparent_customer_idSum(revenue)Accumulative Revenu
1 1105
21253
31351
4344
53544
6266
72745
8788
97930
1091010
1191111
1251212
1351313
1451414

 

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

 

Labels (2)
1 Solution

Accepted Solutions
Kushal_Chawda

Customer:
LOAD * 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
];

Revenue:
LOAD * Inline [
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)
Load customer_id,parent_customer_id,customer_id as CID
Resident Customer;


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

Drop Table Customer;

Left Join(Hierarchy)
Load customer_id as [customer id],
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

View solution in original post

8 Replies
Anil_Babu_Samineni

What is the logic behind for 53 etc.

21253
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
yonghanfi
Contributor II
Contributor II
Author

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

sunny_talwar

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

FieldMapping:
Mapping
LOAD * INLINE [
    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)
LOAD customer_id,
	 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)
LOAD customer_id as customer_id_new,
	 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
LOAD *
Resident tmpCustomer
Where WildMatch(Customer_Depth, 'customer_name*');

DROP Tables Customer, tmpCustomer;

RENAME Field using FieldMapping;

Revenue:
LOAD * INLINE [
    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

stevenkoppens
Partner - Contributor III
Partner - Contributor III

Why do you want to fix this in the script?

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

Kushal_Chawda

Customer:
LOAD * 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
];

Revenue:
LOAD * Inline [
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)
Load customer_id,parent_customer_id,customer_id as CID
Resident Customer;


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

Drop Table Customer;

Left Join(Hierarchy)
Load customer_id as [customer id],
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

sunny_talwar

My man is back...

Kushal_Chawda

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

yonghanfi
Contributor II
Contributor II
Author

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