Top 3 Products Per Region: a Derived Grid with limitPer

A regional merchandising lead asks for the same slide every week: the best three products in each region, so the next order and the next promotion go on what is actually selling there. Built by hand, that slide comes from sorting the full product list by revenue, then going region by region, eyeballing the sorted rows and cutting the list to three. It works until a region is added or a quarter filter is applied to the orders first, and a global top ten is no shortcut: it can fill six of its rows from one region and leave another with none. What the lead needs is top N per group, in JavaScript, recalculated with the filter.

A derived grid can rank within a group, which a plain top N cannot express. Group the orders by region and product, sum the revenue for each pair, sort by revenue and limit to three, with limitPer set to region so the limit resets for every region instead of applying once across all of them. Point it at the orders grid rather than at a dataset of its own, and it reads whatever the filters leave: narrow the orders to a quarter or a single customer and every region’s top three re-derives on the next frame.

That turns a weekly cut-and-paste job into a few lines of configuration that never goes stale between filters. The regional lead applies a filter instead of sending a spreadsheet request, and the top three for every region are right there, recomputed, not re-typed.

Plain JavaScript

import { createGrid } from '@toclocoinc/lattice-grid';

const orders = [
  { id: 1,  region: 'EMEA',     product: 'Atlas Router',   amount: 420 },
  { id: 2,  region: 'APAC',     product: 'Beacon Switch',  amount: 180 },
  { id: 3,  region: 'Americas', product: 'Cirrus Gateway', amount: 960 },
  { id: 4,  region: 'EMEA',     product: 'Beacon Switch',  amount: 310 },
  { id: 5,  region: 'Americas', product: 'Atlas Router',   amount: 540 },
  { id: 6,  region: 'APAC',     product: 'Cirrus Gateway', amount: 275 },
  { id: 7,  region: 'EMEA',     product: 'Cirrus Gateway', amount: 890 },
  { id: 8,  region: 'Americas', product: 'Delta Modem',    amount: 150 },
  { id: 9,  region: 'APAC',     product: 'Atlas Router',   amount: 410 },
  { id: 10, region: 'EMEA',     product: 'Delta Modem',    amount: 220 },
  { id: 11, region: 'Americas', product: 'Beacon Switch',  amount: 730 },
  { id: 12, region: 'APAC',     product: 'Delta Modem',    amount: 95 },
  { id: 13, region: 'EMEA',     product: 'Atlas Router',   amount: 605 },
  { id: 14, region: 'Americas', product: 'Cirrus Gateway', amount: 340 },
  { id: 15, region: 'APAC',     product: 'Beacon Switch',  amount: 515 },
  { id: 16, region: 'EMEA',     product: 'Atlas Router',   amount: 175 },
  { id: 17, region: 'Americas', product: 'Delta Modem',    amount: 820 },
  { id: 18, region: 'APAC',     product: 'Atlas Router',   amount: 260 },
  { id: 19, region: 'EMEA',     product: 'Cirrus Gateway', amount: 450 },
  { id: 20, region: 'Americas', product: 'Atlas Router',   amount: 390 },
];

const book = createGrid(document.querySelector('#orders'), {
  rowKey: 'id',
  columns: [
    { field: 'id',      title: 'Order' },
    { field: 'region',  title: 'Region',  filter: { type: 'set' } },
    { field: 'product', title: 'Product' },
    { field: 'amount',  title: 'Amount',  type: 'number' },
  ],
  rows: orders,
});

// A second grid whose rows come from the first, ranked within each region.
const top3 = createGrid(document.querySelector('#top3'), {
  source: {
    mode: 'derived',
    from: book,            // the grid to read
    follow: 'filtered',    // read whatever the filters leave
    groupBy: ['region', 'product'],
    select: {
      revenue: { of: 'amount', fn: 'sum' },
    },
    sort: [{ col: 'region', dir: 'asc' }, { col: 'revenue', dir: 'desc' }],
    limit: 3,
    limitPer: 'region',
  },
  columns: [
    { field: 'region',  title: 'Region' },
    { field: 'product', title: 'Product' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
  ],
});

Filter the orders grid to Americas and EMEA only, and top3 drops to six rows, three for each of those two regions, APAC’s three gone, with no second query anywhere in the example above.

React

import React from 'react';
import { createGrid } from '@toclocoinc/lattice-grid';
import '@toclocoinc/lattice-grid/css';
import { createLatticeGrid } from '@toclocoinc/lattice-grid/modules/react';

// Build the component once, at module scope, not on every render.
const LatticeGrid = createLatticeGrid({ React, createGrid });

function TopProductsByRegion({ orders }) {
  const [book, setBook] = React.useState(null);

  const orderColumns = [
    { field: 'id',      title: 'Order' },
    { field: 'region',  title: 'Region',  filter: { type: 'set' } },
    { field: 'product', title: 'Product' },
    { field: 'amount',  title: 'Amount',  type: 'number' },
  ];

  const top3Columns = [
    { field: 'region',  title: 'Region' },
    { field: 'product', title: 'Product' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
  ];

  return (
    <>
      <LatticeGrid
        rowKey="id"
        columns={orderColumns}
        rows={orders}
        onGridReady={setBook}
        style={{ height: 320 }}
      />
      {book && (
        <LatticeGrid
          columns={top3Columns}
          source={{
            mode: 'derived',
            from: book,
            follow: 'filtered',
            groupBy: ['region', 'product'],
            select: {
              revenue: { of: 'amount', fn: 'sum' },
            },
            sort: [{ col: 'region', dir: 'asc' }, { col: 'revenue', dir: 'desc' }],
            limit: 3,
            limitPer: 'region',
          }}
          style={{ height: 280 }}
        />
      )}
    </>
  );
}

onGridReady hands back the live orders grid as soon as it mounts, which is what the top three panel’s from needs; the panel itself only renders once that instance exists. Filtering the orders grid, from its own header or a quick filter, re-ranks the panel the same way it does in the plain example, because both read the grid’s public API, not a copy of its rows.

The options used

Top N per group: what the example produced

Running the plain JavaScript example above with createHeadlessGrid from the published package, and printing top3’s rows, gives:

Nine rows, exactly three for each of the three regions, each sorted by revenue within its own region rather than across all of them. Delta Modem never cracks the top three in EMEA or APAC, but ranks second in Americas, which is the case a single global top ten would have hidden.

The derived and chained grids guide covers joins, column profiles, chaining a derived grid from a derived grid, and cross-filtering back into the source in full. For a worked dashboard built the same way, with top sellers and a company-wide tile alongside the per-rep totals, see the sales dashboard demo.