Filter rows and columns based on cell value

You can filter rows and columns based on cell values in a cube in Planning Analytics Workspace.

You can filter rows and columns by count or by value.

You can use filters and sorting capabilities simultaneously. For more information about sorting, see Sort rows and columns.

Note: You can have only one Filter by value or Filter by count filter active at the same time. If you apply another filter of the same type to the same view, the first filter is removed.

Filter by count

Filter by count to filter members in a row or column to show the top or bottom members of a specified count type.

To filter a row or column by count, follow these steps:

  1. Right-click on a row or column header.
  2. Select Filter, and click Filter by count.
  3. In the dialog box, choose the criteria for the filter:
    1. Select if you want the filter to show the top or bottom members of your specified count type.
    2. Type the number of members that you want to see included in the filter results. This value must be numeric.
    3. Select the type of count that you want to filter for. You can choose Items, Percent, or Sum.
    4. If you want to add an additional set of criteria for the filter, click Add. You can add a total of two sets of criteria in each filter. Click the remove icon Icon to remove a set of criteria from the filter to remove a set of criteria from the filter.
  4. Click Apply. The filter is applied to the row or column.

    A row or column with a filter applied has the filter icon Filter icon.

Filter by value

Filter by value to filter the members in a row or column using a comparison operator for a selected value. The value can be a number or a string.

To filter a row or column by value, follow these steps:

  1. Right-click a row or column header.
  2. Select Filter, and click Filter by value.
  3. In the Filter by value dialog box, specify the filter criteria.
    1. From the drop-down menu, select the data type: Number or String.
    2. Depending on the selected type, choose a comparison operator for the filter.

      For numeric values, choose from the following operators: = (equal to), > (greater than), >= (greater than or equal to), < (less than), or <= (less than or equal to).

      For string values, choose from the following operators: Is (equal to), Is not (is not equal to), Contains, Starts with, or Ends with.

    3. Type a number or string that you want the comparison operator to filter by.
    4. To add another set of criteria to the filter, click the Add + button, and repeat steps a to c.

      You can add a total of three sets of criteria that are based on only Number or only String values, or on both types of values, as shown in the following example:

      Example of filter by value dialog box

      Click the remove icon Icon to remove a set of criteria from the filter to remove a set of criteria from the filter.

    5. Select how you want the filter to match the specified criteria.

      Select Match all of the following if you want the filter to include only members that match all of the criteria that you specified.

      Select Match any of the following if you want the filter to include members that match any of the criteria that you specified. You must select this option if your filter is based on both Number and String values.

  4. Click Apply.

    The filter icon Filter icon is applied to a row or column for which the filter is created.

Editing an existing filter

To edit an existing filter, follow these steps:

  1. Right-click on a row or column header that has the filter icon Filter icon.
  2. Select Filter, and click the filter that you want to edit.
  3. Edit the existing criteria in the dialog box.
  4. Click Apply.

Clearing a filter

To clear a filter on a row or column, follow these steps:

  1. Right-click on a row or column header.
  2. Select Clear, and click Filter.