topic Accumulative hierarchy calculation in script in App Development

Accumulative hierarchy calculation in script

yonghanfi — Sat, 16 Nov 2024 19:25:02 GMT

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

Re: Accumulative hierarchy calculation in script

Anil_Babu_Samineni — Fri, 20 Dec 2019 07:34:34 GMT

What is the logic behind for 53 etc.

Re: Accumulative hierarchy calculation in script

yonghanfi — Fri, 20 Dec 2019 07:40:00 GMT

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

Re: Accumulative hierarchy calculation in script

sunny_talwar — Fri, 20 Dec 2019 13:26:24 GMT

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

Re: Accumulative hierarchy calculation in script

stevenkoppens — Fri, 20 Dec 2019 13:33:34 GMT

Why do you want to fix this in the script?

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

Re: Accumulative hierarchy calculation in script

Kushal_Chawda — Fri, 20 Dec 2019 14:57:45 GMT

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

Re: Accumulative hierarchy calculation in script

sunny_talwar — Fri, 20 Dec 2019 14:58:35 GMT

My man is back...

Re: Accumulative hierarchy calculation in script

Kushal_Chawda — Fri, 20 Dec 2019 15:00:27 GMT

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

Re: Accumulative hierarchy calculation in script

yonghanfi — Mon, 23 Dec 2019 08:49:38 GMT

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