Analyze QuickBooks Accounts Receivable (AR) Aging and Overdue Invoices from CSV

How to export A/R Aging Detail or Open Invoices from QuickBooks Online

1. In QuickBooks Online, navigate to Reports and scroll to Who owes you, then select A/R Aging Detail or Open Invoices.
2. Choose your Report period and click Run report.
3. Click the Export icon and export to CSV, or choose Export to Excel. Drop a .csv above for a first chart, or import the .csv or .xlsx file into the xmetric app to build the aging view.

QuickBooks A/R Aging CSV columns used

• Customer: customer or account name owing the balance.
• Transaction Type: Invoice, Credit Memo, or Payment (unapplied credit).
• Date and Due Date: invoice issue date and payment due date.
• Num: invoice number.
• Past Due (or Aging): days overdue as of the report date.
• Amount and Open Balance: original invoice amount and remaining unpaid balance.

Tableau Calculated Fields and SQL for AR Aging Buckets

You can test each formula below in the formula playground, then put [AR Aging Bucket] on Columns and SUM([Open Balance]) on Rows in the xmetric app.

1. Days Past Due (DATEDIFF)

If your export does not include a pre-calculated Past Due column (or if you want to age invoices as of today), compute days overdue from Due Date:

// [Days Past Due]
DATEDIFF('day', [Due Date], TODAY())

2. Standard AR Aging Bucket Classification

// [AR Aging Bucket]
IF [Days Past Due] <= 0 THEN "0. Current (Not Yet Due)"
ELSEIF [Days Past Due] <= 30 THEN "1. 1-30 Days Past Due"
ELSEIF [Days Past Due] <= 60 THEN "2. 31-60 Days Past Due"
ELSEIF [Days Past Due] <= 90 THEN "3. 61-90 Days Past Due"
ELSE "4. 90+ Days Past Due"
END

3. Customer Share of Overdue AR (FIXED LOD)

Find whether your 60+ Days Past Due balance is spread across dozens of small accounts or concentrated in two enterprise clients:

// [Customer Overdue Balance]
{ FIXED [Customer] : SUM(IF [Days Past Due] > 0 THEN [Open Balance] ELSE 0 END) }

Equivalent SQL

WITH clean_ar AS (
  SELECT
    TRIM("Customer") AS customer_name,
    "Transaction Type" AS tx_type,
    "Num" AS invoice_num,
    CAST("Date" AS DATE) AS invoice_date,
    CAST("Due Date" AS DATE) AS due_date,
    DATE_DIFF('day', CAST("Due Date" AS DATE), CURRENT_DATE) AS days_past_due,
    CAST(REPLACE(REPLACE("Open Balance", ',', ''), '$', '') AS DOUBLE) AS open_balance
  FROM quickbooks_ar_aging
  WHERE "Customer" IS NOT NULL
    AND TRIM("Customer") <> ''
    AND TRIM("Customer") NOT LIKE 'Total %'
    AND "Due Date" IS NOT NULL
),
bucketed AS (
  SELECT
    *,
    CASE
      WHEN days_past_due <= 0 THEN '0. Current'
      WHEN days_past_due <= 30 THEN '1. 1-30 Days'
      WHEN days_past_due <= 60 THEN '2. 31-60 Days'
      WHEN days_past_due <= 90 THEN '3. 61-90 Days'
      ELSE '4. 90+ Days'
    END AS aging_bucket
  FROM clean_ar
)
SELECT
  aging_bucket,
  COUNT(DISTINCT customer_name) AS customers_count,
  COUNT(*) AS open_items,
  ROUND(SUM(open_balance), 2) AS total_open_ar,
  ROUND(100.0 * SUM(open_balance) / NULLIF(SUM(SUM(open_balance)) OVER (), 0), 1) AS share_of_ar_pct
FROM bucketed
GROUP BY 1
ORDER BY 1;

QuickBooks AR aging traps that distort collections

• Unapplied payments and credit memos sitting in 90+ Days: When a customer pays an invoice but the bookkeeper records a Receive Payment or Credit Memo without linking it to the specific Invoice, the negative credit sits in 90+ Days while the positive invoice also ages into 90+ Days. Filter to [Open Balance] < 0 to list every unapplied credit that needs to be matched in QuickBooks.
• Using Amount instead of Open Balance: For partially paid invoices, Amount shows the original invoice total while Open Balance shows what is still owed today. Always sum Open Balance.

Prioritize collections with AI agents

Every shelf, filter, and formula in xmetric is a command. An MCP server for Claude and ChatGPT is in development, so your assistant will be able to rank overdue accounts by amount and age from a QuickBooks export. Live connections to Postgres and BigQuery are also coming.