Accumulation across Dimensions

[johnnyjohn]

I've got a tricky one here. 

Trying to understand where the problem with this expression is

RangeSum(Above( Sum(child_value), 0, RowNo()))
/
Sum( TOTAL <parent_mkt_cap> child_value)

So I've got 2 parent orders. 123 and 456. 

The dimension parent_mkt_cap is Mid for 123, and Small for 456. 

Each child belong to either parent 123 or 456. 

The child_time_pct is a relative measure, per parent, of when the child appeared in the timeline of the parent_id. 

So if child_time_pct = 0, it means at the very start of the parent, =1 means its the last child in the parent, =0.5 means it's exactly halfway through the parent. 

What I'm trying to achieve is a cumulative, relative sum of child_value, where the cumulative element is across the child_time_pct, and respects the parent_mkt_cap dimension. 

For example, for 123, it accumulates the child_value, in order of child_time_pct . So the final value for parent_mkt_cap=Mid is (5082+1436+1286+2275+1483+1755+1628). Note that the dimension across which to accumlate is parent_mkt_cap, not parent_id. I then need to divide that value by the total of the child_value under the same parent_mkt_cap to get the measure relative to the value of the parent_mkt_cap 

Any solutions much appreciated.

+------------+----------+-------------+---------------+----------------+
| parent_id  | child_id | child_value | parent_mkt_cap| child_time_pct |
+------------+----------+-------------+---------------+----------------+
| 123        | 45386    | 5082        | Mid           | 0.00           |
| 123        | 92537    | 1436        | Mid           | 0.03           |
| 123        | 21228    | 1286        | Mid           | 0.23           |
| 123        | 52571    | 2275        | Mid           | 0.23           |
| 123        | 6092     | 1483        | Mid           | 0.54           |
| 123        | 14460    | 1755        | Mid           | 0.54           |
| 123        | 44845    | 1628        | Mid           | 1.00           |
| 456        | 135178   | 39759       | Small         | 0.00           |
| 456        | 136857   | 1904        | Small         | 0.50           |
| 456        | 138173   | 364         | Small         | 0.50           |
| 456        | 145301   | 3683        | Small         | 0.50           |
| 456        | 142031   | 1281        | Small         | 0.53           |
| 456        | 152620   | 618         | Small         | 1.00           |
+------------+----------+-------------+---------------+----------------+

[Lisa_P · Accepted solution]

Try this:

RangeSum(Above( Sum(child_value)))
/ Sum( TOTAL <parent_mkt_cap> child_value)