Skip to main content

When working in Tables and Metrics, you can filter your views to make them better for Boards or Charts. Here we tell you all about using filters in Pigment.

 

 

How to Filter in Pigment 

 

From within either a Metric or Table, click the Filter icon and click +Add a Filter. When you’re done creating filters, the number of applied filters is visible next to this icon.

 

When you click + Add a filter, you have the option to:

  • Select a Metric, and then filter the underlying Dimensions by the Metric value
  • Select a specific Dimension and directly filter its items or filter its items based on one of its Properties
Filter Panel in Pigment

 

Filter by Metric 

 

When you select a Metric from the Add a filter menu, this filters Items belonging to the most granular underlying Dimension in the View, depending on the cell values for that Metric.

The options for filtering by Metric depend on your Pivot configuration:

  • the filtered Dimensions are automatically inferred from the grid configuration
  • filters are applied to the most granular Dimensions that are closest to the View

You can also right-click a specific grid header to create a filter that’s based on a Metric. Each new filter you add using this method creates a separate rule in the filter panel.

If you use this shortcut in Tree mode in Views, filtering is applied to the closest Dimension to the grid. For this reason, we recommend that you expand the Tree mode before filtering. When multiple Metrics are available for filtering on Tables, you need to choose just one Metric.

 

Filter by Metric

 

 

Filter Dimension-type Metrics with Variables

In Pigment, when filtering Dimension-type Metric data, you can use a static variable to filter between that Dimension’s Items. This is particularly useful if you want to allow Members to filter these Items directly from the Board.

To apply this type of filter, do the following:

  1. Create a static variable on the required Dimension on your Metric.
  2. (Optional) If you want users to dynamically choose which Items should be filtered, then enable the option that allows Members to change variable values in their session. When you enable this option, the Dimension Item values are displayed in a separate menu next to the Page Selector wherever your Metric is used.
    For more information on creating static variables and using this option, see Item Variables.
     

     
  3. In your Metric, open the Filter panel and create a new filter.

  4. Select the new static variable from the menu, and click Apply.
     



    The Dimension Items on which you can filter are available as a selector beside the Page Selectors. This selector's options can be customized almost as any Page selector options. For more information, see Use Page Selectors to Customize Context in Views and Boards.

     

Filter by Dimension 

 

When you select a Dimension from the Add a filter menu, you can apply a filter based on that Dimension’s Items. Only Dimensions in Rows or Columns can be filtered. After you choose the Dimension you want to filter, you can filter by directly selecting the items. 

 

  • If you select is in, it only shows those items. 
    For example, if a filter is set to Filter Product where Items is in Product A, then the result is that only Product A is shown.
     
  • If you select is not in, it only removes those items. 
    For example, if a filter is set to Filter Product where Items is not in Product A, then the result is that only Product A is the only item not present. When choosing these options it's important to think of the future of the list. If you use the Exclude option, all new items will be shown.

Filter by Dimension Property

 

When you select a Dimension Property from the Add a filter menu, you can filter a Dimension based on values stored within that Dimension’s properties. Depending on the Property data type, you will get different options on the type of rule you can set it.

 

Eligible Dimensions for Filtering

In this example, you can select the Metric value for the Month or the Scenario Dimension. These are considered the most granular Dimension within a View.

 

Use Operators in Filters 

 

After you choose which Dimension you want to filter, you need to choose an operator such as equals ( 🙂, less than (<) or greater than (>), and then you need to enter a value on which to base those operators. These are dependent on the Item type. For example, operators such as Contains exist for Metrics that are Text data types.

 

Understand Rules in Filters

 

When adding multiple rules to a filter, you must set how the rules interact with each other, the options are OR and AND. The AND operator means that all conditions must be met for the rule to be true. For example, if you have two rules:

  • Rule A: The sky is blue
  • Rule B: It is daytime

An AND combination would only be true if both Rule A and Rule B are true - that is, if the sky is blue and it is daytime.

On the other hand, the OR operator means that only one of the conditions needs to be met for the rule to be true. Using the same rules as before, an OR combination would be true if:

  • Rule A is true (the sky is blue)
  • Rule B is true (it is daytime)
  • Both are true

In summary, AND requires all conditions to be satisfied, while OR requires at least one condition to be satisfied.  When setting up multiple rules, you can only use one of these options for the entire filter. This means all rules will be set to the condition of OR or AND.

 

Combine Filtering and Sorting to see Top and Bottom Brackets

 

You can filter items by Metric values such as Integer, number, or dates. Choose to filter items in either the top or bottom categories by specifying the size of the category and whether it should include the highest or lowest values.

When filtering with Metric values that are integers, numbers, or dates, you can choose to filter items in either the highest or lowest value brackets. You can do this by defining the size of the value bracket, and if it should be in the highest or lowest values. 

  1. Select the Metric value.
  2. Use the operators menu to select either is in the top or is in the bottom .
  3. Enter a number to define the size of the bracket.  For example, if you wanted to see your top 5, you select is in the top and then enter 5.

When the filter is activated, it also activates the Sort functionality.  The Sort’s location is determined by if you choose the Top or Bottom. This filtering option ignores blank values.

 

Identify Item Specifics from Opposite Dimensions 

 

When using a Metric value, you must identify the specific Items from the Dimensions opposite to the filtered Dimension. This means if your filtered Dimension is in Rows, you have to show the specifics for the Dimensions in the Columns and vice versa. The position of these is determined by the Pivot panel.

In this example, the Country Dimension is in Rows, the Year and Month Dimensions are in Columns.  If you want to filter multiple items from the opposite Dimensions, you can create multiple filters.

 

 

Filtering with Opposite Dimensions

 

Hide Empty Rows or Columns

When working with Metrics or Tables, you can hide empty columns or rows if they have no data. From within the filter, click Show empty rows and columns to see multiple options. You can hide empty rows or columns, either separately or together, to make your data easier to find.

 

Hiding Empty Rows or Columns 

 

When creating a top rank list, is it possible to have a column to show which rank they are within the list? i.e. In the first example where you filter for top 8, you’d be able to add a column showing each row’s rank 1 through 8?