3.5Advanced groups: Top N, group sort, grouping on a formula
The essentials in 30 seconds
- Top N = the ranking: show only the N best (or worst) groups by their total — 10 best customers, 5 worst-selling products, the rest grouped as “Others”.
- Group sort = value ranking: order groups by their subtotal (largest to smallest), not by name — via the Group Sort Expert.
- Grouping on a formula = segmenting: group on a computed category (bands, quarters, first letter) that does not exist in the database — formulas (3-4) + groups (2-1) combined.
Running mini-case — Farès delivers the top 10 to his management
Farès, management controller in Algiers, presents 200 customers listed alphabetically: management switches off before the end. He enables Top N on the Customer group (10 best by sales total, rest in “Others”), sorts groups by their subtotal descending, and adds a grouping on his band formula (3-4) for VIP/Standard segmentation. Page one of the report now shows the ranked top 10 + the share of “Others”: management sees the essentials in thirty seconds and asks for the detail by band.
Last episode of the Farès thread — from formulas to analysis for decisions.
1. Top N and group sort: the ranking
TOP N / BOTTOM N shows only the N BEST (or N WORST) groups by a total: the 10 customers buying the most, the 5 worst-selling products, the 3 most profitable regions. Via the “Group Sort / Top N” option: choose N, the CRITERION (the summary to rank on — total sales per group) and the direction (Top or Bottom), with the option to group the rest into “Others” (handy: situate the top's weight vs the rest). Prerequisite: a GROUPED report with a SUBTOTAL per group — with no subtotal, no ranking criterion.
GROUP SORT orders the GROUPS themselves by their TOTAL (their subtotal), not by the grouping field: customers from highest to lowest revenue, instead of alphabetical order. Where normal sorting orders rows (or groups by name), group sort ranks by COMPUTED VALUE — essential for any “largest to smallest”. Top N relies on this mechanism.
2. Grouping on a formula: segmenting beyond the database
GROUPING ON A FORMULA groups not on a raw field, but on a FORMULA: by amount BAND (“Small / Medium / Large” via a 3-4 If…Then…Else), by computed PERIOD (quarter derived from month), by FIRST LETTER of the name (Left({Nom}, 1)), by deduced category. Groupings that do NOT exist as fields in the database — great analytical flexibility. Method: first create the formula computing the category, then insert a group ON that formula (the grouping wizard offers it).
These three techniques combine grouping (2-1), summaries (2-2) and formulas (3-1 to 3-4): this is where reporting meets decision support — rankings to spot the essentials, value-ordered rankings, computed segmentations. They often come with summary display (hidden Details, 2-5): the top 10 with its subtotals fits on one page.
🔎 Concrete example — Top 10 customers + band segmentation
Farès combines all three techniques on his sales report.
Top N: Customer group → Group Sort → Top 10 on Sum({Ventes.Montant}), rest in "Others"
Sort: groups ordered by subtotal descending
Segment: group on the @Bracket formula (Small / Medium / Large)Reading: one page of ranked leaders + one computed segmentation.
Lesson: the per-group subtotal is the prerequisite of every ranking.
Section vocabulary
- Top N / Bottom N
- Show only the N best (Top) or N worst (Bottom) groups by a total, with the rest in “Others” or hidden.
Simple example: Farès's top 10 customers by sales total.
What it is not: a descending sort of rows with no group.
Reflex: as soon as you need a ranking, not a list.
- Group sort
- Sort the groups themselves by their subtotal (by value), not by the grouping field (by name).
Simple example: Customers from highest to lowest revenue.
What it is not: groups in alphabetical order for a ranking.
Reflex: rank by value as soon as importance comes first.
- Grouping on a formula
- Group on a formula rather than a raw field: bands, computed periods, first letter, deduced categories.
Simple example: The group on the @Bracket formula (Small / Medium / Large).
What it is not: waiting for the database to hold a “band” column that does not exist.
Reflex: formula first, group on the formula second.
- Ranking
- Result of Top N + group sort: best/worst groups ordered by value — to spot the essentials.
Simple example: The first page of Farès's report, read in thirty seconds.
What it is not: 200 alphabetical customers nobody reads.
Reflex: the ranking on page one, detail after.
- Computed category
- Formula (e.g. If…Then…Else) producing a category — band, period, class — to group on next.
Simple example: The 3-4 VIP / Standard / To follow segmentation.
What it is not: a category retyped by hand outside the report.
Reflex: compute the category, then group on it.
In practice — Build advanced group analyses
- Create a TOP N: on a customer-grouped report with a subtotal, enable Group Sort / Top N — 10 best customers (rest in “Others”).
- Apply a GROUP SORT: order groups by their TOTAL (largest to smallest), not by name.
- Build a GROUPING ON A FORMULA: create a band formula (If amount… “Small/Medium/Large”) then group on it.
- Combine into an analysis report (ranking, segmentation) and check results in preview.
Ready-to-say script — present the ranking to management (30 s)
“Page one: your 10 best customers ranked by revenue, plus the share of the rest grouped as Others. Then band segmentation — VIP, Standard, To follow — computed automatically. The essentials in thirty seconds, detail by band right after.”
Say at the committee; project page one of the ranking.
Copy-paste template — grouping formulas (Crystal syntax)
// @Bracket: group by amount band
If {Ventes.Montant} >= 100000 Then "Large"
Else If {Ventes.Montant} >= 10000 Then "Medium"
Else "Small"
// @Quarter: group by computed quarter
ToText(Year({Ventes.Date}), 0) + "-Q" +
ToText(DatePart("q", {Ventes.Date}), 0)
// @Initial: group by first letter
UpperCase(Left({Clients.Nom}, 1))Freelancer
One Top N + one value sort and your reports become analyses that sell.
SME in Algiers
Farès delivers the ranked top 10: management reads the essentials in thirty seconds.
Project team
Systematize the page-one “ranking + Others” on every sales report.
Beginner
This week: one Top 5 on your data + one grouping by first letter.
✓ Best practices
- Secure group + subtotal BEFORE opening Group Sort (no criterion, no ranking).
- Enable grouping into “Others” to situate the top's weight vs the rest.
- Create and test the category formula before grouping on it.
✗ Common pitfalls
- Launching a Top N with no subtotal per group: no criterion to rank on.
- Confusing row sort and group sort (one orders, the other ranks).
- Grouping on a raw field while expecting a segmentation only a formula gives.
Key takeaways
- TOP N / BOTTOM N: the N best (or worst) groups by a total — ranking, with the rest in “Others”.
- GROUP SORT: order groups by their TOTAL (by value), not by name — the ranking.
- GROUPING ON A FORMULA: group on a COMPUTED category (bands, quarters) missing from the database.
- These techniques combine groups + summaries + formulas: the grouped listing becomes an analysis tool.
48h challenge — apply tomorrow
Deliver 1 ranking page (Top N + value sort + “Others”) of your real data to your manager.
- My report shows a ranking, not an alphabetical list.
- My groups rank by value when importance comes first.
- I segment on computed categories, not just raw fields.
Frequently asked questions
How do I build a ranking (Top N) of best customers or products?
Prerequisite: report GROUPED by the field to rank (customer, product) + SUBTOTAL (Sum) per group — this is the ranking criterion. Method: open “Group Sort / Top N” (Report → Group Sort Expert), choose “Top N” (best) or “Bottom N” (worst), enter N (10 for the top 10), choose the CRITERION (total sales per group), enable “group the rest into Others” (the top situated vs the rest) or hide the rest. Variants: Bottom N for weak points (worst-selling products); Top N + “Others” + grand total to situate the top's weight (the 10 best make X% of the total); combined with group sort for a value ranking; summary display (hidden Details) for a one-page ranking.
How do I sort groups by their total and group on a formula?
GROUP SORT: by default groups follow their FIELD (alphabetical); to order them by VALUE (highest revenue first), use the Group Sort Expert — sort groups by their SUBTOTAL, ascending or descending (prerequisite: the subtotal). Use: rankings, leaderboards. GROUPING ON A FORMULA: to group by a category MISSING from the database — (a) create the FORMULA (band: If {Montant} >= 1000 Then “Large”…; quarter via month; first letter via Left({Nom}, 1)); (b) insert a group ON that formula via the grouping wizard; (c) add subtotals per computed category. Result: band segmentation, quarterly analysis, alphabetical ranking by initial — without touching the database.
What is MANDATORY before creating a Top N?