FilterIntro to control elements

Top N filter

View as Markdown

It’s very common to filter columns by Top N, the top number (N) of rows, based on the filter criteria. This is useful when you want to see the top values in a sorted list, instead of the whole list.

Because of the way Sigma automatically handles aggregations and groupings, the Top N filter may behave slightly differently than what you may have experienced with other products. See the examples and tips below for details.

Examples of Top N

How Applying Top N Changes a Bar Chart

For example, you may be interested in the top 5 stores that move the most merchandise, sorted by state. Before applying the Top N filter, a bar chart may look like this:

Bar chart titled Sum of Quantity by Store State, listing states from California down to Maine sorted in descending order with no Top N filter applied.

Maximize the chart to see its data table underneath. Sometimes it is helpful when applying filters to see how the filters change the table.

After applying Top N to the Sum of Quantity column, the chart looks like this:

Bar chart titled Sum of Quantity by Store State with the Top N filter set to 5 on Sum of Quantity, showing only California, Texas, Florida, Arizona, and North Carolina.

Relationship of Top N Bar Chart to its Table

This example shows a maximized view of a chart, which includes its data table. The Top N filter is on the Sum of Price column. (If you put the filter on the Store City column, Sigma would filter the cities alphabetically.) This results in the list of 10 cities whose stores have the highest total prices of all things sold.

A maximized bar chart titled Sum of Price by Store City with the Top N filter set to 10 on Sum of Price, showing its underlying data table with columns including Store City and Sum of Price.

Top N on a Pivot Table with Multiple Groupings

When there are multiple grouping levels, the Top N filter does not filter the overall top 10 items; instead it filters within a bin defined by the 2nd lowest grouping level. In this example, Top N will apply to the Top 10 Store City rather than the Top 10 Store Region or Store State.

A pivot table grouped by Store Region, Store State, and Store City, with the Massachusetts group outlined to show the Top N filter applied within that state rather than across all cities.

Additional Examples

For more in-depth examples see the Community article How to Use a Top N Filter Parameter for Visualizations.

Also see the video from phData, Understanding Top N Filters in Sigma.

You can use the same process as described above to use the other filters in the same dropdown.

The filter type dropdown open with Top N, Bottom N, Top percentile, and Bottom percentile options listed.

How to Filter Using Top N

  1. Set up the columns on a bar chart.

    • Select a new visualization using + ADD ELEMENT > DATA ELEMENTS > VIZ.

    • Add a column to the Y axis.

    • Add a column to the X axis. Keep the default aggregation.

      Sigma requires an aggregated column when using Top N.

  2. If needed, set the sort criteria.

  3. For the column you want to filter, select its menu > Filter.

    The Store Name column menu open with the Sort and Filter options visible.

    Set the Top N filter on the column you want to be ranked. This can be counter-intuitive. In the example above, you want to show the top 10 Stores, so you would rank on Store Name, not on Sum of Quantity.

  4. In the Filter panel, click the 3 dots to see the options.

    The Filters & Controls panel for the Store Name filter, with the 3-dot menu highlighted to open its options.

  5. Select Top N.

    The Store Name filter's Change filter type menu with the cursor selecting Top N from List, Text match, and Top N options.

  6. Enter the number of results to display.

    The Store Name filter set to Top N with the results count field entered as 10.

  7. Change the default chart header to best describe the filtered chart. For example, change Sum of Quantity by Store Name, which is an internally-produced name, to Top 10 Stores by Quantity Sold.

Change the Ranking Function

By default the Top N filter uses the Rank function. In some cases you may want to change it to the RankDense or RowNumber functions instead. The options are:

  • Rank: Assigns the same rank to duplicate values, and a gap is placed between the ranks. See Rank. Default.
  • Rank Dense: Assigns the same rank to duplicate values, and there is no gap between ranks. See RankDense.
  • Row Number: Ranks according to the highest row numbers. See RowNumber.

To change the ranking function:

  1. From the control’s filter icon at the top right of the control, open the control’s FILTERS & CONTROLS panel.

  2. From the filter’s More menu, select Ranking function.

    The text next to the menu option shows the currently-used function. By default it is Rank.

  3. Select the type of rank you want.

    The Sum of Quantity Top N filter's More menu with Ranking function submenu open, showing Rank, Rank Dense, and Row Number options with Rank checked.

Tips for Top N

  • Maximize the chart so you can see the table fields as you apply filters.

  • Only apply the top N filter to the column that is being ranked.

    For example, you want a bar chart showing the top ten product types. You put Product Type on the X-axis and Sum of Revenue on the Y-axis, then put the Top N filter on the Sum of Revenue column. This gives you the top 10 values of Sum of Revenue grouped by Product Type.

    Otherwise, if you put Top N on the Product Type column the chart will sort the products alphabetically.

  • Sigma requires an aggregated column for Top N.

  • Top N applies only to columns of type text, number, or date.

  • For a text column, the top N is determined by alphanumeric sorting.