How to Create an AR Aging Report in Looker

Knowing who owes you money - and how long they’ve owed it - is one of the most fundamental parts of managing a healthy business. An Accounts Receivable (AR) Aging Report is your best tool for this, but building one in a powerful BI tool like Looker can feel intimidating. This article will walk you through, step-by-step, how to create a detailed and interactive AR Aging Report directly in Looker, transforming raw invoice data into a clear view of your company’s financial health.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is an AR Aging Report?

An Accounts Receivable Aging Report is a critical financial tool that categorizes unpaid customer invoices by the length of time they have been outstanding. It acts as a snapshot, showing you exactly who owes you money and whether their payments are on time or overdue. The primary use of this report is to manage cash flow. By seeing which invoices are significantly past due, your finance and collections teams can prioritize their efforts, focusing on the accounts that pose the greatest risk to your liquidity. The report typically breaks down outstanding invoices into time-based "aging buckets," such as:

• Current: Invoices that are not yet due.
• 1-30 Days Past Due: Invoices that are 1 to 30 days overdue.
• 31-60 Days Past Due: Invoices overdue by 31 to 60 days.
• 61-90 Days Past Due: Invoices overdue by 61 to 90 days.
• 90+ Days Past Due: Invoices that have been outstanding for more than 90 days.

This categorization helps you quickly identify slow-paying customers, spot potential bad debt, and evaluate the effectiveness of your company's credit and collections policies.

Getting Your Data Ready for Looker

Before you can build any report, you need the right ingredients. A useful AR Aging Report relies on having clean, accessible data about your invoices. For this report, your underlying database table (which you'll model in Looker) should contain the following fields at a minimum:

• Customer Identifier: A unique ID or name for each customer (e.g., customer_name).
• Unique Invoice ID: A unique identifier for each invoice (e.g., invoice_id).
• Invoice Date: The date the invoice was issued (e.g., invoice_date).
• Due Date: The date payment for the invoice is due (e.g., due_date).
• Total Amount: The full amount of the invoice (e.g., invoice_amount).
• Amount Remaining: The outstanding balance on the invoice (e.g., amount_due).

In Looker, this data must first be represented in a LookML view. LookML is Looker’s modeling language, and it’s how you define dimensions, measures, and the relationships between your data tables. The logic for our aging buckets will be created directly within the LookML model, making it reusable, consistent, and easy to manage.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step: Creating Aging Buckets in LookML

The core of this report is the logic that calculates how many days an invoice is overdue and assigns it to the correct aging bucket. We'll do this by creating two new dimensions in our LookML view file.

Step 1: Calculate "Days Overdue"

First, we need a dimension that calculates the number of days between an invoice's due date and today. We can accomplish this using a dimension of type: number and Looker's SQL functions.

In your invoices.view.lkml file (or whatever you've named it), add the following LookML code. This creates a dimension named days_overdue.

dimension: days_overdue { type: number description: "Calculates the number of days an invoice is past its due date." sql: DATEDIFF(day, ${due_date_raw}, CURRENT_DATE()) ,, }

Let's break this down:

• type: number: We define this as a number since it represents a count of days.
• sql: DATEDIFF(...): This is where the calculation happens. The DATEDIFF() function's syntax can vary slightly depending on your SQL dialect (e.g., Redshift, BigQuery, Snowflake). This example uses a common syntax.

This simple dimension is the foundation for our aging buckets. For any invoice where the due date has passed, days_overdue will be a positive number.

Step 2: Create the "Aging Bucket" Dimension

Now that we know how many days each invoice is overdue, we can create another dimension to categorize them into our aging buckets. The cleanest way to do this in LookML is with a CASE WHEN statement in the sql parameter. This dimension will be named aging_bucket.

dimension: aging_bucket { type: string case: { when: { sql: ${days_overdue} <= 0 ,, label: "Current" } when: { sql: ${days_overdue} > 0 and ${days_overdue} <= 30 ,, label: "1-30 Days" } when: { sql: ${days_overdue} > 30 and ${days_overdue} <= 60 ,, label: "31-60 Days" } when: { sql: ${days_overdue} > 60 and ${days_overdue} <= 90 ,, label: "61-90 Days" } else: "91+ Days" } }

Breaking down the case statement: The case parameter in LookML provides a more structured way to write these kinds of conditional statements and is generally preferred over a raw CASE WHEN in the sql parameter.

• when: { sql: ..., label: ... }: Each when block defines a condition. If the condition in sql is true, Looker will apply the label.
• sql: ${days_overdue} <= 0: This condition checks if the invoice isn't overdue yet (or is due today). If so, it gets the label "Current".
• sql: ${days_overdue} > 0 and ${days_overdue} <= 30: This condition catches all invoices that are between 1 and 30 days overdue and labels them "1-30 Days".
• The logic continues for the "31-60 Days" and "61-90 Days" buckets.
• else: "91+ Days": This is the catch-all. Any invoice that doesn't meet previous conditions (meaning it's over 90 days late) will be placed in the "91+ Days" bucket.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Define a Measure for the Outstanding Amount

Finally, we need a measure to sum up the outstanding amounts for our report. In the same view file, add this simple measure:

measure: total_amount_due { type: sum sql: ${amount_due} ,, value_format_name: usd_2 }

This measure, total_amount_due, will simply sum the amount_due field for any dimensions you've selected in your report. Using a value_format_name makes the numbers viewer-friendly.

Once you save your LookML changes and push them to production, these new fields will be available for you and your team to use in Looker's Explore interface.

Building the AR Aging Report in a Looker Explore

With the LookML logic in place, building the report for your business users is incredibly simple. This is where the power of Looker's user interface shines.

1. Navigate to Your Explore: Go to the Explore where your invoice data is located.
2. Select Dimensions: From the field picker on the left, select your customer identifier dimension (e.g., Customer Name) and your newly created Aging Bucket dimension.
3. Select Your Measure: Select the Total Amount Due measure. At this point, you'll see a flat table with three columns: a customer, an aging bucket, and an amount.
4. Pivot on Aging Bucket: To get the classic aging report format, find the Aging Bucket field in your "Data" section. Click the gear icon next to it and select "Pivot."
5. Run the Query: Click the "Run" button.

Boom! Looker will now display a report with customer names running down the rows and the aging buckets ("Current," "1-30 Days," "31-60 Days," etc.) spread across the columns. The cells will be populated with the total amount owed by each customer within each specific aging period. You can add column and row totals with a simple click of a checkbox in the "Data" bar.

Taking Your AR Report to the Next Level

A basic report is good, but a great report is interactive and insightful. Here are a few ways to add more power to your Looker AR report.

1. Add Drilling Capabilities

What happens when your CFO wants to see the exact invoices that make up the $50,000 one customer owes in the "90+ Days" bucket? In Looker, you can create drill-downs. In your LookML, add a drill_fields parameter to your total_amount_due measure:

measure: total_amount_due { type: sum sql: ${amount_due} ,, value_format_name: usd_2 drill_fields: [invoice_id, invoice_date, due_date, amount_due] }

Now, when a user clicks on any value in your Total Amount Due column in a report, a pop-up window will appear showing a detailed list of the underlying invoices, including their ID, dates, and individual amounts due. No need for follow-up questions or separate reports.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Schedule and Alert on High-Risk Accounts

You can turn your report into a proactive monitoring tool. Use Looker’s Scheduler to have the AR report emailed to the finance team every Monday morning. Even better, set up an alert. For example, you can create an alert that notifies the collections manager via email or Slack whenever any single customer's balance in the "90+ Days" bucket exceeds a certain threshold (like $10,000).

3. Visualize and Compare

While the table view is standard, try Looker's visualization options. A stacked bar chart can be an effective way to see a customer’s total outstanding balance and the proportion that falls into each aging bucket. You can also create trend charts by adding a date dimension (like Invoice Date by month) to see if your total overdue balance is growing or shrinking over time.

Final Thoughts

Creating an AR Aging Report in Looker gives you a robust, reusable, and interactive tool for managing one of your most critical business assets: your receivables. By defining the business logic in LookML, you create a trusted, single source of truth that your entire team can use to explore data, schedule updates, and drill down into details to get the answers they need to improve cash flow. While mastering LookML to build financial reports from scratch is a valuable skill, we know that not everyone has the time or deep technical exposure needed for complex tools. For teams that need to connect their operational data and get answers quickly without becoming LookML developers, we built Graphed. We turn the experience of creating a report like this into a simple conversation — just securely connect your data sources, ask for "an AR aging report from QuickBooks, pivoted by customer," and Graphed builds the real-time, interactive dashboard for you in seconds.