Ask for "the AR aging table" in Voyager and you'll usually get pointed at something that looks right and isn't. Voyager does keep a table for saved aging reports — but it's exactly that: a store of report snapshots someone generated on demand, sparse and irregular, not a live source of current receivables. Build a query against it expecting today's balances and you'll get whatever the last person happened to save, from whenever they happened to save it. The correct source for live aged receivables is the transaction ledger itself, filtered and bucketed at query time — not a pre-aggregated table.
Here's the conceptual shape of that query, and the two traps that break most first attempts.
The shape of the query
Aged receivables answers one question, per tenant or per property, as of a chosen date: how much is currently owed, and how long has each piece of it been outstanding? That breaks into two parts:
- What's open. Voyager's core transaction table carries every transaction type — charges, payments, credits, reversals — distinguished by a type code, with an open/closed flag marking whether an item still has an outstanding balance. An aging query has to filter to charge-type transactions specifically and to the ones still flagged open; skip either filter and you're aging things that were never owed or that have already been settled.
- How old each piece is. For each open item, the age is the gap between its transaction date and your snapshot date, bucketed into ranges — 0–30, 31–60, 61–90, over 90 is the common shape, though Voyager's own report-writing conventions run finer buckets (30-day increments out to 180, then coarser) for statement-style output.
That's the whole skeleton: join the transaction ledger to the property (and tenant, if you're reporting per-tenant), filter to open charge items, bucket by age against a snapshot date, sum per bucket.
Trap 1: "open" isn't just "unpaid"
The naive definition of "open" is "a charge with no matching payment." That undercounts and overcounts in different directions at once. A charge can carry a partial payment — some of it collected, some still outstanding — so the open amount per line item is the difference between what was charged and what's actually been paid against it, not a binary flag. And unapplied receipts and credit memos complicate the picture further: a payment or credit that exists in the system but hasn't been applied against a specific charge doesn't automatically net that charge down in every view, depending on how the query is built. Before you trust an aging total, be explicit about whether it nets unapplied credits against open charges or reports them as separate lines — both are defensible, but silently picking one changes the number you report.
The upside of Voyager's design here: the open/closed flag on transaction rows already does the heavy lifting of excluding fully-settled items, so you're not reimplementing that logic from scratch. The trap is assuming that flag alone answers "how much is owed" — it tells you an item still has some balance, not what that balance nets to once partial payments and credits are accounted for.
Trap 2: today's date breaks reproducibility
The obvious way to compute age is snapshot_date − transaction_date, where snapshot date defaults to whatever GETDATE() returns. That's fine for a report you run once and read immediately. It quietly breaks the moment anyone needs to reproduce last month's numbers.
Run the same "current" aging query in November about a period that closed in September, and every bucket has shifted by two months' worth of aging relative to what the September report actually showed — because the query recomputed age against today, not against September's close date. If the report needs to match what was reported at the time, or needs to be re-run later for an audit, it has to age against a fixed as-of date, not the date the query happens to execute.
This isn't a hypothetical concern — it's exactly why Voyager's own report-writing conventions treat "as of" as a distinct, named parameter rather than an afterthought: aging reports are built against an explicit as-of token, never against the live clock, specifically so a report generated for a past period stays correct when it's regenerated later. A hand-drafted ySQL query should follow the same discipline: parameterize the snapshot date, don't hardcode GETDATE(), and treat "as of" as an input the caller supplies — today, if you want today, but explicitly.
Per-tenant vs. per-property rollups
One more decision that's easy to skip past: are you reporting aging per tenant, per property, or both? A property-level total is a straightforward sum across tenants, but a per-tenant breakdown needs the tenant join carried through every bucket, not just the top-line total — otherwise you can produce a property figure that reconciles but a tenant-level detail that doesn't add back up to it, usually because the join dropped tenants with zero open balance instead of showing them at zero. Decide the grain up front, and make sure whichever level of detail you're not reporting still reconciles to the one you are, even if it never appears in the output.
It's also worth deciding, before you build the query, whether reversed or voided charges need explicit handling. A charge that was reversed shouldn't still be open in an aging report, but depending on how the reversal was recorded — a new offsetting transaction versus a status change on the original — the open-item filter alone may or may not exclude it correctly. This is exactly the kind of install-specific behavior worth confirming against your own data rather than assuming it works the same way everywhere.
Draft it against the real schema, not a guess
Aged receivables is a small query with a lot of ways to be subtly wrong: the wrong source table (saved snapshots instead of live transactions), an incomplete definition of "open," and an implicit dependency on today's date that only shows up when someone tries to reproduce last quarter's report. PropETL's SQL Query Assistant carries the standard Voyager schema and a library of execution-verified ySQL patterns — including live AR aging built from the transaction ledger rather than the saved-snapshot table — so the join chain, the open-item filter, and the as-of parameterization are right before you run anything. It's included in every tier, including the free 7-day trial — no card required.