Aging Buckets
Aging Buckets: Distribution of receivables by age.
Formula
Bucket % = AR in bucket / Total AR × 100Computable definition
The canonical formula in the GASP formula grammar. The MCP server compiles it to SQL over the datasets listed below.
sum(field.outstanding_amount) / total(sum(field.outstanding_amount))Unit: percent (the formula yields an unscaled ratio)
Breakdowns
- Reported with one row per bucket of
field.snapshot_date - field.invoice_date(inclusive bounds):current0 to 30,days_31_6031 to 60,days_61_9061 to 90,days_90_plus91 and over, each read against the whole population (share of total)
Notes: Reported per age bucket: outstanding amount on invoices whose age (days from invoice date to the period-end snapshot) falls in the bucket over the whole outstanding balance. Evaluate at period end.
Data requirements
Grain: End-of-month receivables snapshot · Frequency: Monthly · Aggregation: snapshot (month) · Staged: no · Main source: Billing.
| Field | Dataset | Type |
|---|---|---|
invoice_date | invoice | date |
outstanding_amount | invoice | decimal |
snapshot_date | invoice | date |
Example SQL (Postgres)
SELECT _d.bucket AS bucket, (CAST((SELECT COALESCE(SUM(outstanding_amount), 0) FROM invoice WHERE snapshot_date = :period_end AND CASE WHEN (snapshot_date - invoice_date) BETWEEN 0 AND 30 THEN 'current' WHEN (snapshot_date - invoice_date) BETWEEN 31 AND 60 THEN 'days_31_60' WHEN (snapshot_date - invoice_date) BETWEEN 61 AND 90 THEN 'days_61_90' WHEN (snapshot_date - invoice_date) >= 91 THEN 'days_90_plus' END = _d.bucket) AS DOUBLE PRECISION) / NULLIF((SELECT COALESCE(SUM(outstanding_amount), 0) FROM invoice WHERE snapshot_date = :period_end), 0)) AS aging_buckets
FROM (SELECT 'current' AS bucket UNION ALL SELECT 'days_31_60' AS bucket UNION ALL SELECT 'days_61_90' AS bucket UNION ALL SELECT 'days_90_plus' AS bucket) _d From an agent: with the GASP MCP server, call
lookup_metric({ "name": "Aging Buckets" }) or generate_query({ "metric": "Aging Buckets", "adapter": "<your adapter JSON>" }).