A percentage that quietly exonerates
On a report built to catch till fraud, every rate had a capped top and an uncapped bottom. It made everybody look better than they were, and it truncated the small, frequent discounts first — which is what the pattern actually looks like.
Of the wrong numbers we found this week, this is the one that bothers me most, because of what the report is for.
A loss-prevention report shows rates. What share of this person's sales end in a refund. How often a paid order gets voided on their till. How many staff discounts they put through against how many orders they took. Rates, not counts, because a busy person will always have more of everything and a rate is what makes two people comparable.
The denominators were counted properly — a grouped query over the whole period, every order. The numerators were read as the length of the evidence lists: the refunds table, the voids table, the discounts table. And those lists were capped, at 400, 600 and 300 rows, so that the report would draw.
So on any window big enough — and there is no limit on how wide a window you can ask for — a rate came out lower than the truth. Every rate. For everybody.
The sort order made it worse
This is the detail I would want to know if it were my venue. The discount evidence was ordered by value, biggest first, so that the worst offenders appeared at the top of the list. Entirely reasonable for a list somebody reads.
But when the same list is truncated and then counted, ordering by value means the small ones are the ones thrown away. And somebody skimming a till does not take one large discount that shows up in a report. They take a lot of small ones. The exact pattern the report was built to surface is the exact pattern the truncation deleted.
Nothing anywhere on the page said any rows had been dropped.
What to ask of any staff report
Not just ours. If you use a report to have a difficult conversation with somebody, it is worth knowing two things about every percentage on it:
- Were the top and the bottom counted the same way? A rate where one half is a complete count and the other is a list length is not a rate. Pick one person, one week, and count their refunds by hand against what the report says. A week is short enough that no cap will bite, which means the two should agree exactly. If they do, widen to a quarter and try again — that is where a cap shows itself.
- Does the report ever tell you it left something out? If it has never once said "showing 300 of 918", it either never truncates or it truncates silently. Only one of those is comfortable.
What we changed
Every numerator now comes from its own complete grouped count, and the evidence lists stay capped, because they are for reading. The report also carries a small block saying which lists were capped, how many rows were shown and what the caps were — so if you are about to sit down with somebody on the strength of a number, you can see exactly what the number was made of.
The wider point: an error that flatters everybody is the hardest kind to notice, because nobody complains about being made to look good and nobody investigates a rate that came in lower than they feared.