ABC classification
Classify items into A, B and C by their share of a total, such as products by sales, with reusable AQL functions.
ABC classification sorts items by a measure, biggest first, and groups them by how much of the total they cover together. It's the 80/20 idea: a few items make up most of the volume.
- A: the top items that together cover the first 70% of the total.
- B: the next items, up to 90%.
- C: the rest, the long tail.
For example, it shows which products bring in most of your sales, so you know where to focus stock and promotion.
How it works
A product's class doesn't depend on its own sales alone. It depends on how much the products bigger than it already cover.
| Product | Sales | Running total, biggest first | Share of total | Class |
|---|---|---|---|---|
| P1 | 50 | 50 | 50% | A |
| P2 | 20 | 70 | 70% | A |
| P3 | 15 | 85 | 85% | B |
| P4 | 10 | 95 | 95% | C |
| P5 | 5 | 100 | 100% | C |
The functions compute this in two parts:
- Running total, biggest first:
window_sum(m, order: m | desc())adds this item and every item bigger than it. - Total:
m | of_all(products, keep_filters: true)is the total over all items, with your filters kept. If you filter to one year, the classes are computed for that year.
Step 1: Add the function library
Add this AqlFunctionModule to your project.
AqlFunctionModule Abc {
// Cumulative share of the total, items sorted by pMeasure descending.
function CumulatedPct {
param pMeasure {
type: 'metric'
}
param pItemModel {
type: 'model'
}
return_type: 'number'
definition: @aql
const pct = safe_divide(
window_sum(pMeasure, order: pMeasure | desc()),
pMeasure | of_all(pItemModel, keep_filters: true)
);
case(when: pct > 1, then: 1, else: pct)
;;
}
// 'A' up to pA, 'B' up to pB, 'C' after. null bounds use 0.7 and 0.9.
function Class {
param pMeasure {
type: 'metric'
}
param pItemModel {
type: 'model'
}
param pA {
type: 'number'
}
param pB {
type: 'number'
}
return_type: 'text'
definition: @aql
case(
when: CumulatedPct(pMeasure, pItemModel) <= coalesce(pA, 0.7), then: 'A',
when: CumulatedPct(pMeasure, pItemModel) <= coalesce(pB, 0.9), then: 'B',
else: 'C'
)
;;
}
}Step 2: Import it and add the metrics
Import the library into your dataset, then define the measure to classify by and the ABC metrics.
Dataset ecommerce {
models: [orders, order_items, products]
function_module Abc: Abc
metric sales {
label: 'Sales'
type: 'number'
definition: @aql order_items | sum(order_items.quantity * products.price) ;;
}
metric abc_pct {
label: 'ABC Cumulative %'
type: 'number'
definition: @aql Abc.CumulatedPct(sales, products) ;;
}
metric abc_class {
label: 'ABC Class'
type: 'text'
definition: @aql Abc.Class(sales, products, 0.7, 0.9) ;;
}
}Parameters
Abc.Class
| Parameter | Type | What it is |
|---|---|---|
pMeasure | metric | The measure to classify by, such as sales. It must add up across items, like a sum or a count. |
pItemModel | model | The model of the items you classify, such as products. |
pA | number | Upper bound of class A, from 0 to 1. null uses 0.7. |
pB | number | Upper bound of class B, from 0 to 1. null uses 0.9. |
Abc.CumulatedPct
Takes pMeasure and pItemModel as above. Returns the item's cumulative share of the total, from 0 to 1.
Use it in a table
Put the item in the table and sort by the measure, biggest first.
explore {
dimensions {
product_name: products.name
}
measures {
sales: sales,
abc_pct: abc_pct,
abc_class: abc_class
}
sorts {
sales desc
}
}Limitations
- The item must be in the table, for example
products.name. The class is computed from the rows of the result. - Classes are fixed to A, B and C. Change the bounds with
pAandpB. - The measure must add up across items. An average or a distinct count doesn't give a meaningful cumulative share.