(v24) Displaying Top/Bottom Records

Displaying Top/Bottom Records

This applies to: Managed Dashboards, Managed Reports

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.

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

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.

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.

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.

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

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.

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

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.

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

For more information, see:

• Select and Group Data for a Metric Set

• Group Members During Analysis

• Sort and Filter Metric Data

• List of Formulas

• Symphony Metric Sets