Measured on @objectstack/service-analytics@17.0.0-rc.1 while building win-rate widgets in the HotCRM reference app (hotcrm#593 / hotcrm#656, where the full reproduction lives).
Mechanism
A dataset measure carrying 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 propagates that:
const vals = d.of.map((name) => num(row[name]));
if (vals.some((v) => v === null)) return null; // absent input poisons the whole ratio
Reproduction, through the real executor on shipped metadata
{ 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:
| lead_source |
won_count |
lost_count |
decided_count |
win_rate |
| content |
2 |
1 |
3 |
66.7% |
| referral |
1 |
1 |
2 |
50.0% |
| partner |
1 |
— |
1 |
100% |
| cold_call |
— |
1 |
1 |
— |
cold_call won nothing and lost one. The correct answer is 0%. What renders is blank — visually identical to "no data for this row", which is the opposite of what the row means.
Note the asymmetry: partner (all wins) renders fine, because there the numerator is the measure that exists. The rows that blank are exactly the worst-performing ones. For a dashboard, that is the least acceptable direction for the bias to run.
Why we did not patch it in the consumer
The obvious fix is ?? 0 in the widget or a coalesce in the measure. We deliberately did not, and think the reasoning generalises: a consumer-side patch has to be repeated by every author of every ratio widget, forever, and forgetting it is silent — the number just reads as missing. It is also not always correct: absent and zero are genuinely different for sum/avg, and only the executor knows which aggregate produced the gap.
Instead the current behaviour is pinned by an app-side assertion written to fail when the platform fixes this, so the fix surfaces as a red test rather than as dashboards silently changing.
Suggested direction
- Fill on merge, by aggregate kind.
mergeByDimensions knows which measure it is merging. A count that produced no group for a dimension value present in another measure's result is unambiguously 0 — "how many rows matched" has an exact answer when the answer is none. sum over no rows is arguably 0; avg/min/max are genuinely null.
- Or: let
derived declare its null semantics (missingAs: 'zero').
We recommend 1, and the reason is worth stating explicitly: it requires no author to remember anything. Option 2 looks more precise and more "explicit", but it redistributes the decision to every future author, and the failure mode of forgetting is silent. For AI-authored metadata — where the author is a model pattern-matching from neighbouring declarations — a correctness switch that must be remembered is equivalent to a correctness switch that will eventually be missed. Option 1 has no such surface.
Workaround shipped 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). Every shipped table also prints the raw counts beside the rate, so a blank reads as "0 of 1" rather than "no data".
Related: hotcrm#656 (full detail), hotcrm#593, #4698.
Measured on
@objectstack/service-analytics@17.0.0-rc.1while building win-rate widgets in the HotCRM reference app (hotcrm#593 / hotcrm#656, where the full reproduction lives).Mechanism
A dataset measure carrying 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 propagates that:Reproduction, through the real executor on shipped metadata
Grouped by
lead_source:cold_callwon nothing and lost one. The correct answer is 0%. What renders is blank — visually identical to "no data for this row", which is the opposite of what the row means.Note the asymmetry:
partner(all wins) renders fine, because there the numerator is the measure that exists. The rows that blank are exactly the worst-performing ones. For a dashboard, that is the least acceptable direction for the bias to run.Why we did not patch it in the consumer
The obvious fix is
?? 0in the widget or acoalescein the measure. We deliberately did not, and think the reasoning generalises: a consumer-side patch has to be repeated by every author of every ratio widget, forever, and forgetting it is silent — the number just reads as missing. It is also not always correct: absent and zero are genuinely different forsum/avg, and only the executor knows which aggregate produced the gap.Instead the current behaviour is pinned by an app-side assertion written to fail when the platform fixes this, so the fix surfaces as a red test rather than as dashboards silently changing.
Suggested direction
mergeByDimensionsknows which measure it is merging. Acountthat produced no group for a dimension value present in another measure's result is unambiguously0— "how many rows matched" has an exact answer when the answer is none.sumover no rows is arguably 0;avg/min/maxare genuinely null.deriveddeclare its null semantics (missingAs: 'zero').We recommend 1, and the reason is worth stating explicitly: it requires no author to remember anything. Option 2 looks more precise and more "explicit", but it redistributes the decision to every future author, and the failure mode of forgetting is silent. For AI-authored metadata — where the author is a model pattern-matching from neighbouring declarations — a correctness switch that must be remembered is equivalent to a correctness switch that will eventually be missed. Option 1 has no such surface.
Workaround shipped 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). Every shipped table also prints the raw counts beside the rate, so a blank reads as "0 of 1" rather than "no data".Related: hotcrm#656 (full detail), hotcrm#593, #4698.