ABC classification

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.

library/aql-functions/abc.aml

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 pA and pB.
• The measure must add up across items. An average or a distinct count doesn't give a meaningful cumulative share.