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.

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.

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 modelStep 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)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.

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
Saldo > 0 in the receivables expressions.vFechaAnalisis = Today() variable at the start of the script and use it consistently.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.




