Measured while building the win-rate widgets in #593, on @objectstack/service-analytics@17.0.0-rc.1. Filed unassigned; nothing in #593 depends on it being fixed, but any future ratio widget will hit it.
What happens
A dataset measure with its own filter is executed as a separate sub-query and merged back onto the selected dimensions (executeSelection → mergeByDimensions). mergeByDimensions only writes a measure's column onto rows the sub-query returned. A GROUP BY over a filtered row set produces no group at all for a dimension value the filter excludes entirely — so the measure comes back absent, not 0.
computeDerived then does:
const vals = d.of.map((name) => num(row[name]));
if (vals.some((v) => v === null)) return null; // ← absent input poisons the whole ratio
so a derived measure over that input evaluates to null.
Reproduction (shipped metadata, real executor)
opportunity_metrics declares:
{ name: 'won_count', aggregate: 'count', filter: { stage: 'closed_won' } },
{ name: 'lost_count', aggregate: 'count', filter: { stage: 'closed_lost' } },
{ name: 'decided_count', aggregate: 'count', filter: { stage: { $in: [ 'closed_won', 'closed_lost' ] } } },
{ name: 'win_rate', derived: { op: 'ratio', of: [ 'won_count', 'decided_count' ] } },Grouped by lead_source over the shipped seeds, through the real AnalyticsService.queryDataset:
| lead_source |
won_count |
lost_count |
decided_count |
win_rate |
| content |
2 |
1 |
3 |
66.7% |
| web |
2 |
1 |
3 |
66.7% |
| referral |
1 |
1 |
2 |
50.0% |
| partner |
1 |
— |
1 |
100% |
| cold_call |
— |
1 |
1 |
— |
| advertisement |
— |
1 |
1 |
— |
cold_call lost one deal and won none. The right answer is 0%. What comes back is a blank won_count and a blank win_rate — visually identical to "no data for this row", which is the opposite of what the row means. The all-wins rows (partner) are fine, because there the numerator is the one that exists.
Pinned as current behaviour in test/win-loss-capture.test.ts (a source that only ever lost reports no wins — a measured platform gap), so a fix will show up as that test going red rather than silently changing dashboards.
Why this is worth fixing at the source
The obvious consumer-side patch is ?? 0 in the widget or a coalesce in the measure. That is exactly the lenient-consumer habit this repo keeps paying for: it would spread across every ratio widget anybody writes, each author has to remember it, and forgetting is silent — the number just reads as missing. It also is not always right: absent and zero are genuinely different for a sum/avg measure, and only the executor knows which one a given aggregate meant.
The asymmetry is also the wrong way round for a demo or a review: the group with the worst performance (0 wins) is the one that renders blank.
Suggested direction (for the platform to decide)
- Fill counts with 0 on merge.
mergeByDimensions knows the measure it is merging; a count measure that produced no group for a dimension value present in another measure's result is genuinely 0. sum over no rows is arguably 0 too; avg / min / max are genuinely null.
- Or: let
derived state its null semantics — e.g. derived: { op: 'ratio', of: [ ... ], missingAs: 'zero' } — so the author declares the intent at the one place the intent is known, and it is still declared-and-enforced rather than patched in a consumer.
Option 1 needs no author to remember anything, which is the property that matters most for AI-authored metadata; option 2 is more precise but reintroduces a per-author decision. Recommending option 1 for count specifically, since "how many rows matched" has an unambiguous answer when the answer is none.
Workaround in place today
decided_count is its own filtered count rather than derived: { op: 'sum', of: [ 'won_count', 'lost_count' ] } — the derived-sum spelling would blank the denominator for any rep who has never lost a deal (a 100% win rate, the row you least want to hide). And every shipped win-rate table prints won_count / lost_count / decided_count next to win_rate, so a blank rate reads as "0 of 1", not as "no data".
Measured while building the win-rate widgets in #593, on
@objectstack/service-analytics@17.0.0-rc.1. Filed unassigned; nothing in #593 depends on it being fixed, but any future ratio widget will hit it.What happens
A dataset measure with its own
filteris executed as a separate sub-query and merged back onto the selected dimensions (executeSelection→mergeByDimensions).mergeByDimensionsonly writes a measure's column onto rows the sub-query returned. AGROUP BYover a filtered row set produces no group at all for a dimension value the filter excludes entirely — so the measure comes back absent, not0.computeDerivedthen does:so a derived measure over that input evaluates to
null.Reproduction (shipped metadata, real executor)
opportunity_metricsdeclares:Grouped by
lead_sourceover the shipped seeds, through the realAnalyticsService.queryDataset:cold_calllost one deal and won none. The right answer is 0%. What comes back is a blankwon_countand a blankwin_rate— visually identical to "no data for this row", which is the opposite of what the row means. The all-wins rows (partner) are fine, because there the numerator is the one that exists.Pinned as current behaviour in
test/win-loss-capture.test.ts(a source that only ever lost reports no wins — a measured platform gap), so a fix will show up as that test going red rather than silently changing dashboards.Why this is worth fixing at the source
The obvious consumer-side patch is
?? 0in the widget or acoalescein the measure. That is exactly the lenient-consumer habit this repo keeps paying for: it would spread across every ratio widget anybody writes, each author has to remember it, and forgetting is silent — the number just reads as missing. It also is not always right: absent and zero are genuinely different for asum/avgmeasure, and only the executor knows which one a given aggregate meant.The asymmetry is also the wrong way round for a demo or a review: the group with the worst performance (0 wins) is the one that renders blank.
Suggested direction (for the platform to decide)
mergeByDimensionsknows the measure it is merging; acountmeasure that produced no group for a dimension value present in another measure's result is genuinely0.sumover no rows is arguably 0 too;avg/min/maxare genuinely null.derivedstate its null semantics — e.g.derived: { op: 'ratio', of: [ ... ], missingAs: 'zero' }— so the author declares the intent at the one place the intent is known, and it is still declared-and-enforced rather than patched in a consumer.Option 1 needs no author to remember anything, which is the property that matters most for AI-authored metadata; option 2 is more precise but reintroduces a per-author decision. Recommending option 1 for
countspecifically, since "how many rows matched" has an unambiguous answer when the answer is none.Workaround in place today
decided_countis its own filtered count rather thanderived: { op: 'sum', of: [ 'won_count', 'lost_count' ] }— the derived-sum spelling would blank the denominator for any rep who has never lost a deal (a 100% win rate, the row you least want to hide). And every shipped win-rate table printswon_count/lost_count/decided_countnext towin_rate, so a blank rate reads as "0 of 1", not as "no data".