Qlik Sense Accounts Receivable Aging: Turn Your Delinquent Portfolio into Immediate Collection Decisions - featured image

Publications

Qlik Sense Accounts Receivable Aging: Turn Your Overdue Portfolio into Immediate Collection Decisions

Hector Pincheira

Hector Pincheira

CIO | CTO
Digital Strategy & Technology Innovation

items

51% of companies in Latin America face delays collecting their invoices. That is no small matter: it is the difference between growing and surviving. In Chile, some 100,000 small and mid-sized businesses close every year, and 40% of them do not fail for lack of sales but for lack of liquidity. They have customers, they have contracts, they have invoices issued. But the money is not in the account because nobody is watching receivables with the urgency they deserve. If your company has a QlikSense accounts receivable aging still unbuilt, you are making financial decisions with your eyes closed.

QlikSense Accounts Receivable Aging - Turn overdue receivables into collections
Accounts Receivable Aging — Turn overdue receivables into collections

This article is not for the accountant looking to reconcile the general ledger. It is for the manager who needs to know, today, right now, which customers are draining liquidity without anyone having called them yet.

The Real Problem: You Have the Data, but Not the Visibility

Most companies have some system that records invoices: SAP, a local ERP, even Excel. The problem is not the absence of data; it is that the data is dead. Nobody activates it to make decisions.

What is the usual scenario? The head of finance asks for an overdue receivables report. The analyst opens Excel, cross-references invoice tables, calculates days past due with manual formulas, produces a file that arrives 48 hours later with yesterday's data. By the time the manager reads it, two more days have gone by. A customer who appeared in the 31-60 day bucket is now in 61-90. Nobody called in time.

According to the Coface 2024 report, the average payment term in Latin American micro-enterprises exceeds 41 days, when the agreed term rarely goes beyond 30. That means most of your customers are already paying late structurally, and probably nobody in your organization is monitoring it with the granularity required.

Average Days Sales Outstanding (DSO) in companies without analytical visibility runs between 55 and 75 days. Companies with active AR dashboards bring it below 40. That difference, in a company with $5 million in receivables, can mean more than $800,000 of additional operating cash.

QlikSense accounts receivable aging - financial receivables analysis
Real-time receivables visibility is the difference between a company that manages its cash and one that suffers it.

The Solution: Building the Aging Model in QlikSense

Step 1: The Right Data Model

Before writing a single expression, the data model has to be built properly. For AR aging you need at least these tables and key fields:

  • Facturas: NumDocumento, Cliente, FechaFactura, FechaVencimiento, MontoTotal, Saldo
  • Pagos: NumDocumento, FechaPago, MontoPagado
  • Customers: ClienteID, Nombre, Segmento, Ejecutivo, LímiteCredito

The most critical field you must calculate in the load script is DiasVencidos. Do not calculate it in front-end expressions: do it in the script so it is indexable and filterable.

// ============================================
// LOAD SCRIPT: AR with calculated aging
// ============================================
// Payments table used to match balances
Pagos:
LOAD
    NumDocumento,
    Sum(MontoPagado) AS TotalPagado
FROM [lib://DataLayer/Pagos.qvd] (qvd)
GROUP BY NumDocumento;
// Main invoice table with calculated aging
CxC:
LOAD
    NumDocumento,
    ClienteID,
    FechaFactura,
    FechaVencimiento,
    MontoTotal,
    MontoTotal - If(IsNull(Peek('TotalPagado')), 0, Peek('TotalPagado')) AS Saldo,
    
    // Days past due: only invoices not fully paid and past their due date
    If(
        MontoTotal - If(IsNull(Peek('TotalPagado')), 0, Peek('TotalPagado')) > 0
        AND Today() > FechaVencimiento,
        Today() - FechaVencimiento,
        0
    ) AS DiasVencidos,
    
    // Aging bucket with numeric prefix for correct sorting
    If(
        MontoTotal - If(IsNull(Peek('TotalPagado')), 0, Peek('TotalPagado')) <= 0,
            '5 - Paid',
        If(Today() <= FechaVencimiento,
            '0 - Current',
        If(Today() - FechaVencimiento <= 30,
            '1 - 1 to 30 days',
        If(Today() - FechaVencimiento <= 60,
            '2 - 31 to 60 days',
        If(Today() - FechaVencimiento <= 90,
            '3 - 61 to 90 days',
            '4 - Over 90 days'))))
    ) AS BucketAging
    
FROM [lib://DataLayer/Facturas.qvd] (qvd)
WHERE Exists(NumDocumento);  // Only invoices with activity in Pagos, or adjust to your model
💡 Key point: The numeric prefix in BucketAging (‘0 – Current’, ‘1 – 1 to 30 days’…) guarantees that the visual objects sort the buckets correctly in ascending order without needing any special sorting.

Step 2: Set Analysis for Receivables Metrics

Once the DiasVencidos and BucketAging fields are in the model, building the metrics is precise and efficient. Here are the fundamental expressions you should have as master measures:

// ============================================
// MASTER EXPRESSIONS - AR AGING
// ============================================
// 1. Total outstanding receivables (all invoices with a balance)
=Sum({<BucketAging -= {'5 - Paid'}>} Saldo)
// 2. Overdue receivables (excludes current and paid)
=Sum({<BucketAging -= {'0 - Current', '5 - Paid'}>} Saldo)
// 3. Receivables by specific bucket - example 1 to 30 days
=Sum({<BucketAging = {'1 - 1 to 30 days'}>} Saldo)
// 4. Critical receivables (over 90 days)
=Sum({<BucketAging = {'4 - Over 90 days'}>} Saldo)
// 5. % of overdue receivables over total
=Sum({<BucketAging -= {'0 - Current', '5 - Paid'}>} Saldo)
/ Sum({<BucketAging -= {'5 - Paid'}>} Saldo)
// 6. DSO - Days Sales Outstanding (last 12 months)
=Sum({<BucketAging -= {'5 - Paid'}>} Saldo)
/ (Sum({<FechaFactura = {">=$(=AddMonths(Today(),-12))"}>} MontoTotal) / 365)
// 7. Number of customers with critical debt (>90 días)
=Count(Distinct {<BucketAging = {'4 - Over 90 days'}>} ClienteID)
// 8. Concentration: % of critical receivables vs top 5 customers
=Sum({<BucketAging = {'4 - Over 90 days'}, 
       ClienteID = {"=Rank(Sum({<BucketAging={'4 - Over 90 days'}>}Saldo))<=5"}>} Saldo)
/ Sum({<BucketAging = {'4 - Over 90 days'}>} Saldo)
💡 Key point: The DSO expression is one of the most important financial KPIs for management. In QlikSense you can make it dynamic by connecting it to a period selector, so the CFO can compare current DSO against DSO for the same month last year with a single click.

Step 3: Executive AR Aging Dashboard

Dashboard design is not secondary. It is the difference between an analysis nobody uses and one that becomes the first screen the finance manager opens every morning.

Recommended layout — 3 strategic zones:

Zone 1 — KPI header (top row): Five KPI boxes in a row: Total Outstanding Receivables, Total Overdue Receivables, Current DSO, % of Receivables at Critical Risk, and Number of Delinquent Customers. Each KPI with a comparison against the previous month using color arrows (green/red).

Zone 2 — Central visualization: On the left, a stacked bar chart by BucketAging with traffic-light colors (green=current, yellow=1-30, orange=31-90, red=over 90). On the right, a customer treemap colored by overdue amount: block size represents the balance and color the criticality of the overdue period.

Zone 3 — Management table: An actionable table with columns: Customer, Account Executive, Total Balance, Overdue Balance, BucketAging, Days Past Due, Phone, and a calculated «Alert» column showing traffic-light icons. This table must export to Excel with an action button so the collections team uses it daily.

QlikSense accounts receivable aging executive financial dashboard
A well-designed aging dashboard turns invoice data into a concrete collections agenda for the commercial team.

Business Impact: Numbers That Justify the Investment

Companies that implement active AR visibility with tools like QlikSense do not just improve collections: they change their posture toward credit and customers.

The most immediate measurable impact is DSO reduction. A company with a DSO of 65 days that brings it down to 42 frees up working capital equivalent to 23 days of sales. In a company with $10 million in annual sales, that is $630,000 of additional cash available, without needing new customers or raising prices.

The second impact is early detection. With an active dashboard, the team can identify which customers are accumulating debt in the 1-30 day bucket before they migrate to the critical bucket. A call on day 20 past due has an 85% recovery rate. On day 91, that rate falls to 43% according to corporate credit industry data.

The third impact is strategic: knowing which customers generate sales but also generate risk. There are customers who look successful in the sales report yet are permanently delinquent. QlikSense lets you cross margins with payment behavior to make credit decisions based on real profitability, not on invoicing volume.

Common Mistakes When Implementing Aging in QlikSense

⚠️ Common error 1: Calculating DiasVencidos in the front-end expression instead of the script. This keeps filters from working properly on the field and makes the bucket visualizations unusable as dimensions.
⚠️ Common error 2: Not separating partially paid invoices from fully paid ones. A $1 balance on a $50,000 invoice can show up as «critical overdue receivables» and distort the whole report. Always filter by Saldo > 0 in the receivables expressions.
⚠️ Common error 3: Using the QlikSense server operating system date instead of a controlled reference date. If the model loads at midnight, the «today» calculations can vary. Define a vFechaAnalisis = Today() variable at the start of the script and use it consistently.
⚠️ Common error 4: Not including the account executive or account owner as a dimension. Without that column, the dashboard is informative but not actionable. The purpose of aging is not to report the past but to assign concrete management responsibility.

Best Practices for a Successful Implementation

The first practice that makes a difference is to automate the daily load. An aging model refreshed weekly loses 80% of its value. The reload must run every morning before 8:00 AM so the collections team starts the day with the previous day's data.

The second is to create strategic bookmarks. Set up predefined bookmarks the manager can activate with one click: «Critical receivables today», «Top 10 debtor customers», «Due this week». This turns the dashboard into an action tool, not an exploration tool.

The third practice is to connect aging with the credit limit. Add the credit limit per customer to the model and calculate the % utilization. A customer using 95% of their limit with invoices in the 61-90 day bucket is a double red flag: not only delinquent, but an active credit risk.

Finally, combine aging with customer profitability. AR systems manage risk, but the business needs to know whether that risk is worth it. Integrate margins by customer from your costing system and cross them with payment behavior. The result may surprise you: some of your «best customers» by volume are the ones that cost you most once you include the financial cost of their delinquency.

Strategic Conclusion: Receivables That Are Not Managed Are Lost

In finance, late information is not just inconvenient: it has a real cost. Every day an overdue invoice stays invisible in your systems is one more day of capital tied up that could be funding your next investment, paying suppliers or reducing your bank credit line.

The QlikSense accounts receivable aging I described here is not a six-month project. With the right data model and the expressions I gave you, you can have a functional, actionable dashboard in under two weeks. The question is not whether you can build it; it is how much longer you will keep operating without it.

Companies that lead their sector do not do it because they have more customers or better prices. They do it because they decide faster, with better information. Your accounts receivable portfolio is one of the largest assets in your company. Treat it as such.

Do you want to implement a receivables management system like this in your company? At hectorpincheira.com you will find more resources on how to turn your financial data into real competitive advantage.

Most Read

QlikSense Hierarchy: parent-child chart of accounts expanded by levels
QlikSense Hierarchy: the Chart of Accounts That Doesn't Add Up
QlikSense Sum TOTAL: share of each family over the total
QlikSense Sum TOTAL: the Percentage That Changes When You Filter
SAP Business One Inventory Revaluation - featured image of the tutorial
SAP Business One Inventory Revaluation: Fix Wrongly Loaded Costs
Empty chamber with wooden benches: the counterweight that required being present to be used
The Veto You Had to Say in Person: Your Decisions With No Counterweight
SAP Business One customizations - featured image of the pre-upgrade audit tutorial
SAP Business One Customizations: Pre-Upgrade Audit
SAP Business One Customer Concentration - featured image of the tutorial
SAP Business One Customer Concentration: Measure Your Risk with SQL

RELATED ARTICLES