Description of Problem:
Expression columns are evaluated at leaf granularity and then aggregated with
the column's chosen aggregate. This works well for a single ratio via the
built-in weighted mean aggregate (e.g: #2324).
aggregates: { "conversion": ["weighted mean", "traffic"] }
→ correctly yields sum(customers)/sum(traffic) at every group_by level.
However, there's no way to correctly aggregate a derived metric built from
two independently-weighted ratios with different weight columns, e.g.:
conversion = customers / traffic
conversion_py = customers_py / traffic_py
conversion_delta = (conversion - conversion_py) * 100
No single built-in aggregate can express "weighted mean of x with weight w1
minus weighted mean of y with weight w2" — the result is not a weighted mean
of (x - y) under any single weight vector when w1 and w2 differ per row.
Potential Solutions:
- Allow expressions to be evaluated after aggregation, operating on
already-aggregated column values within a pivoted view (i.e. a
"post-aggregation expression" concept), or
- A new built-in aggregate that accepts two (numerator, denominator) pairs
and computes the difference of the two resulting ratios at each pivot
level, or
- Support composing a View's aggregated output directly as input to a new
set of expressions without round-tripping through a second Table.
Happy to provide a minimal repro dataset/config if useful.
Description of Problem:
Expression columns are evaluated at leaf granularity and then aggregated with
the column's chosen aggregate. This works well for a single ratio via the
built-in
weighted meanaggregate (e.g: #2324).aggregates: { "conversion": ["weighted mean", "traffic"] }
→ correctly yields sum(customers)/sum(traffic) at every group_by level.
However, there's no way to correctly aggregate a derived metric built from
two independently-weighted ratios with different weight columns, e.g.:
conversion = customers / traffic
conversion_py = customers_py / traffic_py
conversion_delta = (conversion - conversion_py) * 100
No single built-in aggregate can express "weighted mean of x with weight w1
minus weighted mean of y with weight w2" — the result is not a weighted mean
of (x - y) under any single weight vector when w1 and w2 differ per row.
Potential Solutions:
already-aggregated column values within a pivoted view (i.e. a
"post-aggregation expression" concept), or
and computes the difference of the two resulting ratios at each pivot
level, or
set of expressions without round-tripping through a second Table.
Happy to provide a minimal repro dataset/config if useful.