My expectation does not exist

You want the row count between 10 and 20, a sum within 5 % of last month, no gap in a sequence, a ratio under a threshold. None of these is an expectation — and all of them are one query away from set is empty.

The move #

Keep the query that computes the number. Add the condition that means something is wrong — as a HAVING or a WHERE on a derived table — and expect set is empty. Two things happen: the test passes exactly when the rule holds, and when it fails the failure message shows the actual value, because the row that came back carries it.

“I expect COUNT(*) to be between 10 and 20” — as you fill it in on the Tests page of CAT Studio, and as YAML:

• Form view
• YAML

Tests:
- Name: Between 10 and 20 active campaigns
  Data source: CRM
  Query: |
    SELECT  COUNT(*) AS ActiveCampaigns
    FROM    dbo.Campaign
    WHERE   Status = 'Active'
    HAVING  COUNT(*) NOT BETWEEN 10 AND 20
  Expectation: set is empty

Passes: no row. Fails: one row, ActiveCampaigns = 27, right there in the message — not just “failed”.

More of the same move #

A number within a range of another number — this month’s revenue within 5 % of last month’s:

SELECT  ThisMonth, LastMonth
FROM    (SELECT SUM(CASE WHEN Period = @this THEN Amount END) AS ThisMonth,
                SUM(CASE WHEN Period = @last THEN Amount END) AS LastMonth
         FROM fact.Revenue WHERE Period IN (@this, @last)) t
WHERE   ABS(ThisMonth - LastMonth) > 0.05 * LastMonth

No gaps in a sequence — invoice numbers:

SELECT  InvoiceNo + 1 AS MissingFrom
FROM    dbo.Invoice i
WHERE   NOT EXISTS (SELECT 1 FROM dbo.Invoice j WHERE j.InvoiceNo = i.InvoiceNo + 1)
        AND InvoiceNo < (SELECT MAX(InvoiceNo) FROM dbo.Invoice)

A ratio under a threshold — at most 1 % of orders without a shipping address:

SELECT  COUNT(*) AS Orders, SUM(CASE WHEN ShipAddressId IS NULL THEN 1 ELSE 0 END) AS NoAddress
FROM    fact.Orders
HAVING  SUM(CASE WHEN ShipAddressId IS NULL THEN 1 ELSE 0 END) > 0.01 * COUNT(*)

A measure in a range (DAX) — the model’s total within what the business signed off:

EVALUATE FILTER(ROW("Total", [Total Sales]), NOT ([Total] >= 1200000 && [Total] <= 1300000))

Each of them: the query returns the offending numbers, the expectation is set is empty; the test looks exactly like the first one with the query swapped.

Why CAT does not have more expectations #

An expectation that encoded “between 10 and 20” would be one of hundreds — between, not between, within percent, monotonic, unique, … — each a small language to learn, each weaker than SQL. The five that exist say what a result must look like; the rule stays in the query, where you can run it in any client, explain it to a colleague and extend it without reading a manual. set row count is the one exception, kept because “exactly N” is so common; even that one is HAVING COUNT(*) <> N in disguise.

And because the rule lives in the query, an assistant can write it for you — see the tip above.

Related #

• Which expectation when · Find problems with set is empty
• Set is empty — the reference, including how much of the result is read.