The Overdue Invoices dashboard displays the total amount from your finalized invoices that are either pending or failed and have passed their due date. It does not include voided or disputed invoices. At a glance, you can see what your customers owe you, providing quick insight into the state of your revenue collection.
To access the Overdue invoices dashboard:
- Navigate to the “Analytics” section; and
- Access the “Overdue invoices” Dashboard.
Our overdue balance calculation considers the following factors:
- Finalized invoices;
- With pending or failed as a payment status; and
- A due date that is past;
It is not taking into account invoices that are disputed or voided.
Overdue invoices dashboard
To access the Overdue invoices dashboard:
- Navigate to the “Analytics” section; and
- Access the “Overdue invoices” Dashboard.
Our overdue balance calculation considers the following factors:
- Finalized invoices;
- With pending or failed as a payment status; and
- A due date that is past;
It is not taking into account invoices that are disputed or voided.
Overdue invoices dashboard
LAGO_URL="https://api.getlago.com"
API_KEY="__YOUR_API_KEY__"
curl --location --request GET "$LAGO_URL/api/v1/analytics/overdue_balance?currency=USD" \
--header "Authorization: Bearer $API_KEY" \
--header 'Content-Type: application/json' \
The generated amounts are in cents, making it compatible with different currencies.
--- Start date of the query is the organization creation date
WITH organization_creation_date AS (
SELECT
DATE_TRUNC('month', o.created_at) AS start_month
FROM organizations o
WHERE o.id = '__YOUR_ORGANIZATION_ID__'
),
--- Generate a number of date series in the future
all_months AS (
SELECT
generate_series(
(SELECT start_month FROM organization_creation_date),
DATE_TRUNC('month', CURRENT_DATE + INTERVAL '10 years'),
interval '1 month'
) AS month
),
--- Get total of overdue invoices
payment_overdue_invoices AS (
SELECT
DATE_TRUNC('month', payment_due_date) AS month,
i.currency,
COALESCE(SUM(total_amount_cents), 0) AS total_amount_cents,
array_agg(DISTINCT i.id) AS ids
FROM invoices i
LEFT JOIN customers c ON i.customer_id = c.id
WHERE i.organization_id = :organization_id
AND i.payment_overdue IS TRUE
---AND c.external_id = 'hooli_1234' --- FILTER BY CUSTOMER
GROUP BY month, i.currency, total_amount_cents
ORDER BY month ASC
)
--- Get overdue balances month over month
SELECT
am.month,
#{select_currency_sql},
SUM(invs.total_amount_cents) AS amount_cents,
jsonb_agg(DISTINCT invs.ids) AS lago_invoice_ids
FROM all_months am
LEFT JOIN payment_overdue_invoices invs ON am.month = invs.month
WHERE am.month <= DATE_TRUNC('month', CURRENT_DATE)
---AND am.month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '11 months') --- LAST 12 MONTHS
--- AND cd.currency = 'EUR'
AND invs.total_amount_cents IS NOT NULL
GROUP BY am.month, invs.currency
ORDER BY am.month;
