Accumulative hierarchy calculation in script

[yonghanfi]

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

[Anil_Babu_Samineni]

What is the logic behind for 53 etc.

2

1

2

53

[yonghanfi]

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]

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 · Accepted solution]

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]

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