Floemdata
BI & Reporting

NULL Values Silently Shift Report Totals

NULL is not zero. It quietly shifts averages, counts and joins in reports. How to spot it, how to handle it, and where Floemdata stands today.

Floemdata · · 4 min read
A balance tipped toward the pan holding only a dashed empty ring, while the other pan holds a solid disc

Say four orders have discounts of 10, 20, empty and 30; the figures are a fictional example. What is the average discount? Most tools say 20, because they leave the empty value out of the calculation. Treat the empty one as zero and you get 15. Both numbers are consistent, but if one silently replaces the other in the same report, nobody can find out why.

Empty is not zero

NULL means “unknown”. Zero means “we know, and the value is zero”. If a report does not tell them apart, the figure shifts without a sound.

Four places where it shifts the number

  • Averages: NULL rows drop out of the denominator. In the example above the denominator is 3, not 4.
  • Counts: the number of rows and the number of filled values differ. COUNT(*) gives 4, COUNT(discount) gives 3.
  • Filters: a condition like region != 'Aegean' also leaves out rows whose region is empty; the expected “everything except Aegean” comes out short.
  • Joins and lists: a single NULL in a NOT IN list can make the query return no rows at all; empty keys do not match each other.

What to do

  • For each measure, decide what empty means and write it down: unknown, not applicable, or zero?
  • If you mean zero, convert it to zero explicitly in the load step; do not hide it in the report query.
  • Show the share of filled rows next to the report: “discount calculated from 3 orders.”
  • Add a check that counts rows with empty values; if the number jumps, something changed in the source.

Where Floemdata stands

In Floemdata's YouTube connector, Gold calculates the difference between daily snapshots and leaves the difference for the first day, which has no earlier snapshot, as NULL rather than zero; this behaviour is written in the product documents. The connector has not yet been tried with a real account. For SAP Business One we did not compile a field-by-field list of which fields arrive empty from the source and how reports treat them; this is not verified.

To start, pick a column that is averaged in the report you look at most, count its empty rows, and add “calculated from how many rows” to the report. Questions are welcome at info@floemdata.com or via Floemdata.

← All guides