LastNonEmpty
LastNonEmpty(expr, dateCol)
Semi-additive: per chart group, evaluates expr per date and returns the value at the latest date where it is non-empty — closing balances, latest inventory counts, current headcount. No additivity requirement on the operand (a Min or Avg balance is fine).
Composes under Calculate (a predicate picks the last non-empty date within that filter); DateShift cannot compose with a snapshot — it would fabricate a date that never happened. Charted on its own date axis, each bucket shows its period-end value automatically (the same behavior PeriodEndSnapshot gives you explicitly).
When to use it
Section titled “When to use it”- Stocks and levels, where summing over time is meaningless: inventory on hand, account balance, open pipeline, headcount.
- Current readings on KPI cards — the latest count, whatever date it landed on.
Example
Section titled “Example”ClosingStock := LastNonEmpty(Sum(Inventory.OnHand), Inventory.CountedAt)Under the hood
Section titled “Under the hood”Three small CTEs: value-per-date, the last date that has a value, and the pick:
// On a KPI card, filtered to one warehouseClosingStock := LastNonEmpty(Sum(Inventory.OnHand), Inventory.CountedAt)WITH "__sqx_0" AS ( -- value per raw count date SELECT "Inventory"."counted_at" AS "kd", SUM("Inventory"."on_hand") AS "val" FROM "inventory" AS "Inventory" GROUP BY 1),"__sqx_0_last" AS ( -- latest date that actually has a value SELECT MAX("kd") AS "kd" FROM "__sqx_0" WHERE "val" IS NOT NULL)SELECT "__sqx_0"."val" AS "ClosingStock"FROM "__sqx_0"JOIN "__sqx_0_last" ON "__sqx_0_last"."kd" = "__sqx_0"."kd"On a grouped chart, both CTEs also carry the group keys — each region/warehouse picks its own last non-empty date.