|
Cumulative Sum By Max
The cumulative sum of the sum of the value across siblings ordered by the max of the weight column.
Sample
Given this table showing Key, Date, Value, Day, and RowPerDay fields.
|
Key |
Date |
Value |
Day |
RowsPerDay |
|
A |
2018-01-01 |
1 |
1 |
3 |
|
B |
2018-01-01 |
2 |
1 |
3 |
|
C |
2018-01-01 |
4 |
1 |
3 |
|
D |
2018-01-02 |
4 |
2 |
1 |
|
E |
2018-01-03 |
5 |
3 |
4 |
|
F |
2018-01-03 |
6 |
3 |
4 |
|
G |
2018-01-03 |
7 |
3 |
4 |
|
H |
2018-01-03 |
8 |
3 |
4 |
Provide a weight column that when summed gives the order of the nodes. For example, create a new calculated column based on this expression:
AverageDay = [Day]/[RowsPerDay]
Make Value-CumSumByMax as the new aggregate column (CumulativeSumByMax) based on Value as the source column and AverageDay as the weight column.
The Date – Day column serves as the breakdown.
Result
The nodes are sorted on the max of the weight column, and then the sum of the value column is accumulated across.
|
Date - Day |
AverageDay |
Day |
RowsPerDay |
Value |
Value-CumSumByMax |
|
1 |
0.33 |
3 |
9 |
7.00 |
7.00 |
|
2 |
2.00 |
2 |
1 |
4.00 |
37.00 |
|
3 |
0.75 |
12 |
16 |
26.00 |
33.00 |
Computation details:
|
Day 1: 7.00 |
|
|
Day 2: 7.00 + 4.00 + 26.00 = 37.00 |
|
|
Day 3: 7.00 + 26.00 = 33.00 |
|