Displaying top/bottom records

Contents[Hide]

1. Overview

You can filter your metric set to display the top or bottom 'N' number of items, or group the smallest or largest items together into an 'other' group (with a customizable caption). Any other filters are applied first before filtering or grouping the top/bottom items.

For example, you can decide to display only the top 5 values, or only the top 4 values plus an Other value that groups and aggregates all the smaller values together.

Tip
Another option is to use the TOP or BOTTOM subset function in a formula metric set.

2. Set the top / bottom measure

If you want to limit or filter your result to a certain number of items based on a measure's values, you can set up a top/bottom measure.

In the Data Analysis Panel, click the Edit icon.

Edit the metric set settings
Edit the metric set settings

Set Top / Bottom Measure to the measure you want to determine the top or bottom records.

Set Top and/or Bottom to the number of top or bottom items or values you want to display.

Set the values
Set the values

Items with the same value are "tied". You can decide how to handle these values:

  • Select Exclude Ties to display exactly the number of items you set, even if some values are tied.
  • Select Include All Ties to display all of the items with the top/bottom 'N' values, e.g., the top three values, even if there are four items because two items are tied.
  • Select Include All Ties In Last Position to display only the number of items you set, plus any other items with the same value as the last item.

Click the submit button at the bottom of the dialog to apply the settings.

Example showing Top 5 values
Example showing Top 5 values

Note
This setting does not sort the values. You may now want to sort by the same measure.

3. Set the other group measure

You can set up an 'other' group to group multiple rows, columns, or data points together into one if their measure value meets certain criteria. This can be set up together with a top/bottom measure as described above, or on its own.

In the Data Analysis Panel, click the Edit icon.

Edit the metric set settings
Edit the metric set settings

Set Other Group Measure to the values you want to determine the items to group together.

Other group settings
Other group settings

In case there are multiple hierarchies in your metric set, set Hierarchy to whichever displayed hierarchy's values should be grouped together into one.

Set Caption to whatever the new group should be called, e.g., Other.

Set Category to determine if the smallest (Bottom), largest (Top), or values closest to the middle should be grouped together.

Upper/Lower Boundary Category allows you to choose whether to group items together based on:

  • Items with values above or below a Fixed Value
  • The items with values within the top or bottom Percentage of Total
  • A specified Number of Items

Click the submit button at the bottom of the dialog to apply the settings.

Pie chart example with many small values grouped together
Pie chart example with many small values grouped together

Note
When using an OLAP data source, the grouped value may be missing until you assign the measure an aggregator.

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri