Sales Leaderboard: Top Five Reps From a Derived Grid

Every Monday the sales manager asks for the same thing: who’s on top this week. Someone groups the orders by rep, sums the revenue, sorts it and keeps the first five, by hand or in a spreadsheet pulled from the same data the orders table already shows. It is right on Monday. Then the manager asks for just this quarter, or just EMEA, and the whole exercise starts again, while the rep who actually leads this quarter sits in sixth place on last week’s list. Keeping the top N rows in a JavaScript grid that follows the filter removes the exercise.

A derived grid removes the rebuild instead of scheduling it. Build the leaderboard as a second grid whose rows come from the first: group the orders by rep, reduce each group to a revenue sum, sort by that sum and keep the top five, pointed at the orders grid rather than at a query of its own. It reads whatever the orders grid’s filters leave, so narrowing the orders table to a quarter or a region re-derives the leaderboard on the next frame, with nothing wired between them by hand.

That is a leaderboard that cannot fall out of date, because it is not a report run on a schedule, it is a view of the table as it stands right now. Filter to EMEA and the top five become EMEA’s top five. Filter to last quarter and the ranking becomes last quarter’s. The manager gets a current answer every time, not the answer from whenever someone last ran the query.

Plain JavaScript

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

const orders = [
  { id: 1,  rep: 'Priya Shah',   amount: 420 },
  { id: 2,  rep: 'Mateo Ruiz',   amount: 180 },
  { id: 3,  rep: 'Priya Shah',   amount: 960 },
  { id: 4,  rep: 'Jin Park',     amount: 310 },
  { id: 5,  rep: 'Priya Shah',   amount: 540 },
  { id: 6,  rep: 'Owen Clarke',  amount: 275 },
  { id: 7,  rep: 'Mateo Ruiz',   amount: 890 },
  { id: 8,  rep: 'Jin Park',     amount: 150 },
  { id: 9,  rep: 'Priya Shah',   amount: 410 },
  { id: 10, rep: 'Lena Fischer', amount: 220 },
  { id: 11, rep: 'Mateo Ruiz',   amount: 730 },
  { id: 12, rep: 'Owen Clarke',  amount: 95 },
  { id: 13, rep: 'Jin Park',     amount: 605 },
  { id: 14, rep: 'Lena Fischer', amount: 340 },
  { id: 15, rep: 'Mateo Ruiz',   amount: 515 },
  { id: 16, rep: 'Priya Shah',   amount: 175 },
  { id: 17, rep: 'Owen Clarke',  amount: 820 },
  { id: 18, rep: 'Lena Fischer', amount: 260 },
  { id: 19, rep: 'Jin Park',     amount: 450 },
  { id: 20, rep: 'Sana Ibrahim', amount: 60 },
];

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

// A second grid whose rows come from the first.
const topReps = createGrid(document.querySelector('#top-reps'), {
  source: {
    mode: 'derived',
    from: book,            // the grid to read
    follow: 'filtered',    // read whatever the filters leave
    groupBy: 'rep',
    select: {
      revenue: { of: 'amount', fn: 'sum' },
    },
    sort: [{ col: 'revenue', dir: 'desc' }],
    limit: 5,
  },
  columns: [
    { field: 'rep',     title: 'Rep' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
  ],
});

Six reps sold this batch of orders; topReps holds only the top five, because limit trims the sorted groups before the grid ever renders a row.

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 TopReps({ orders }) {
  const [book, setBook] = React.useState(null);

  const orderColumns = [
    { field: 'id',     title: 'Order' },
    { field: 'rep',    title: 'Rep', filter: { type: 'set' } },
    { field: 'amount', title: 'Amount', type: 'number' },
  ];

  const repColumns = [
    { field: 'rep',     title: 'Rep' },
    { field: 'revenue', title: 'Revenue', type: 'number' },
  ];

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

onGridReady hands back the live orders grid as soon as it mounts, which is what the leaderboard’s from needs; the leaderboard itself only renders once that instance exists. Filtering the orders grid, from its own header or a quick filter, re-ranks the leaderboard 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

The top N rows it produced

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

Sana Ibrahim, the sixth rep in the batch, sold 60 and does not appear: the limit keeps exactly five rows, the five with the highest revenue, and nothing more. Filter the orders grid to a single rep and topReps narrows to just that rep’s row, with the same revenue figure, because the leaderboard has no total of its own to fall out of step.

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 per-rep totals alongside a company-wide tile, see the sales dashboard demo.