...

Group and summarize a tree structure table

 

I have a tree structure Excel table, where Epic column is the highest data layer.

1

Code

Epic

Stories

Task

Hour

2

1

E1


3

1.1

St1

4

1.1.1

Ts1

10

5

1.1.2

Ts2

20

6

1.2

St2

7

1.2.1

Ts21

5

8

1.2.3

Ts22

2

9

2

E2


10

2.1


0

Task: Group rows by Epic, the highest data layer, and summarize Hours column while keeping the Code column. Below is the expected result:

G

H

I

1

Code

Epic

Hours

2

1

E1

37

3

2

E2

0

Use SPL XLL to do perform the computation:

=spl("=E(?1).group@i(Epic!=null).new(Code,Epic,ifn(~.sum(Hour),0):Hours)",A1:E10)

group@i function performs the conditional grouping. Symbol ~ represents the current group; new()function creates a new table; ifn() function returns the first non-null member (return 0 when the aggregation result on the current group is null).

Source:https://stackoverflow.com/questions/78157345/how-to-create-excel-pivot-table-from-a-table-with-hierarchical-columns


 
 •  91 View  •