Concepts → Aggregate Filter
About an aggregate filter
An aggregate filter is a pill in the Aggregate Filter tray in the Insight panel of the Analyzer. In the Filter panel for the pill, you can specify the aggregation type, the filter operator, and one or more filter values.
The result is a filter expression. The filter expression includes an aggregation that excludes rows after the grouping from the insight query. An aggregate filter is synonymous with a SQL HAVING
clause.
In the Analyzer for an insight, you can specify one or more aggregate filters for a supported visualization.
The Rich Text visualization does not support aggregate filters.
For both an Incorta Analyzer Table and an Incorta Analyzer View, the Analyzer only supports the Listing Table and Aggregated Table visualizations.
In order to create or edit the following with the Analyzer, you must belong to a group assigned with necessary security role(s) AND be the owner of the object or have Edit access rights to the object:
- an insight on a dashboard tab
- an Incorta Analyzer Table in a physical schema
- an Incorta Analyzer View in a business schema
The objects are a dashboard, physical schema, and business schema, respectively. An aggregate filter is not visible to a user with View or Share access rights to a dashboard, physical schema, or business schema.
Filter expression context for an aggregate filter
The parent object of an aggregate filter pill is either an insight, an Incorta Analyzer Table, or an Incorta Analyzer View. The parent object governs the filter expression context and, in doing so, determines the evaluation and application of the filter expression.
An aggregate filter pill is either a column or a formula. When a column from a physical schema table or runtime business view is an aggregate filter pill, you specify the aggregation type, the filter operator, and a filter value within the Filter panel so as to specify the aggregate filter expression. Alternatively, you can add a formula from the Data panel to the Aggregate Filter tray and use the Formula Builder to create a formula expression. A formula expression can contain Boolean logic, but must contain a filter expression for the aggregation filter.
An aggregate filter for an Incorta Analyzer Table
An Incorta Analyzer table is a derived table, meaning that it queries data from a physical schema or a business schema. An Incorta Analyzer table allows for one or more aggregate filters for an Aggregated Table.
An aggregate filter for an Incorta Analyzer View
An Incorta Analyzer View is a type of runtime business view. Unlike a business schema view, an Incorta Analyzer View allows for one or more aggregate filters for an Aggregated Table. As a runtime business view, an Incorta Analyzer View is only available in the runtime processing of the Analytics Service.
An aggregate filter for an insight
The Analytics Service applies an aggregate filter in the query plan predicate, the HAVING
clause, of an insight query. A query plan relates to one or more measures for the given insight. The Analytics Service evaluates the query at runtime when the insight renders on the dashboard tab.
You can create an aggregate filter for most visualizations that support two and three dimensions. Here are the visualizations that support an aggregate filter:
Two dimensions
- Aggregated Table
- Pie
- Donut
- Sunburst
- Spider
- Combo
- Combo Dual Axis
- Bubble (requires 3 measures)
- Scatter
- Funnel
- Pyramid
- Map
- Bubble Map
Three dimensions
- Column
- Stacked Column
- Percent Column
- Bar
- Stacked Bar
- Percent Bar
- Area
- Stacked Area
- Percent Area
- Line
- Stacked Line
- Percent Line
- Pie Donut
- Combination
- Radial Bar
- Packed Bubble
- Line Time Series
- Time Series
- Dual X-Axis
- Advanced Map
- Sankey
Supported filterable columns based on the filter expression context of the aggregate filter
The following table details the filterable columns that you can use or reference for each context. These columns can be data-backed columns or formula columns:
Filterable columns | Context: Incorta Analyzer Table | Context: Incorta Analyzer View | Context: Insight |
---|---|---|---|
Business_Schema.Incorta_View.Column | ✔ | ||
Business_Schema.Incorta_View.Formula_Column | ✔ | ||
Business_Schema.View.Column | ✔ | ✔ | ✔ |
Business_Schema.View.Formula_Column | ✔ | ✔ | ✔ |
Physical_Schema.Table.Column | ✔ | ✔ | ✔ |
Physical_Schema.Table.Formula_Column | ✔ | ✔ | ✔ |
Physical_Schema.Incorta_Analyzer_Table.Column | ✔ | ✔ | |
Physical_Schema.Incorta_Analyzer_Table.Formula_Column | ✔ | ✔ | |
Physical_Schema.Incorta_SQL_Table.Column | ✔ | ✔ | |
Physical_Schema.Materialized_View.Column | ✔ | ✔ | ✔ |
Physical_Schema.Materialized_View.Formula_Column | ✔ | ✔ | ✔ |
The joins between physical schema tables and the query plan for the insight measure work together to determine if an aggregate filter uses a filterable column. For example, you can specify a physical schema column in a parent table as an aggregate filter for a measure in a child table. In contrast, a physical schema column from a child table is not a filterable column for a parent table as an aggregate filter, even when it is the foreign key reference. The behavior is the same for a formula as an aggregate filter. The following are the filterable columns that you can use or reference in a formula column when creating an aggregate filter pill:
- The filterable column is a dimension or measure column, that is, it exists as pill in either the Grouping Dimension or Measure trays in the Analyzer.
- The filterable column has a sibling column from the same physical schema table, and that sibling is a dimension or measure.
When a measure and/or dimension pills are columns or formula columns from an Incorta Analyzer View, all filterable columns must be from the Incorta Analyzer View. Similarly, if the measures and/or dimensions pills are not columns or formula columns from an Incorta Analyzer View, all filterable columns must not be from an Incorta Analyzer View.
Types of aggregate filters in the Analyzer
When creating an aggregate filter pill, you can use either a formula or a column of any data type. When a column from a physical schema or a runtime business schema is an aggregate filter pill, you specify the filter operator and one or more filter values within the Filter panel in the Analyzer to complete the filter expression. Selected columns can be data-backed columns that reference data that persists to shared storage or formula columns.
Alternatively, you can add a formula from the Data panel to the Aggregate Filter tray and use the Formula Builder to create the filter expression. For a formula, you specify a filter expression. The context of the aggregate filter determines the types of columns that you can use or reference. For more information, see Supported filterable columns based on the filter expression context of the aggregate filter.
Aggregate filter pill as a Column
An aggregate filter pill represents a filter expression. The filterable column in the filter expression can be a key, dimension, or measure column. The filterable column can be a data-backed column or formula column.
A filter expression for the aggregate filter pill consists of the filterable column, a filter operator, and one or more filter values. To configure the Filter properties, select the pill in the Aggregate Filter tray in the Insight panel. In the Filter panel, configure the following properties to specify the filter expression.
Property | Control | Description | Comments |
---|---|---|---|
Column name | label | Displays the fully-qualified name of the selected column pill. | You can select the information icon to view more details about the column |
Column label | text box | Displays the editable label for the column pill. | |
Aggregation | drop down list | Select the aggregation type to use in the filter expression | |
Operator | drop down list | Select the filter operator. | |
Values | Radial | The Between filter operator supports entering a range of values. | |
Values | text box | Enter scalar values in the text box and use the + control to add the filter values. You can add more than one filter value, but only select one for the filter expression. | You cannot enter a variable. You must use a formula to specify a variable as a filter value in a filter expression for an aggregate filter. |
Disable filter | toggle | Enable to disable the filter expression. | This property overrides the toggle in the Filter Values panel. A disabled filter appears as grey pill in the related filter tray. |
Aggregate filter pill as a Formula
You use the Formula Builder to create a formula expression for an aggregate filter pill that is a formula. Depending on the filter operator, a formula expression can itself evaluate specific conditions and values. A filter value for an aggregate filter can be a scalar value or a referenceable variable. For more details, see Valid referenceable variables in an aggregate filter.
The following are the properties of an aggregate filter that uses a formula.
Property | Control | Description | Comments |
---|---|---|---|
Column label | text box | Displays the editable label for the column pill. | |
Formula | text box | Select to open the Formula Builder to create the filter expression. | |
Disable filter | toggle | Enable to disable the filter expression. | This property overrides the toggle in the Filter Values panel. A disabled filter appears as grey pill in the related filter tray. |
Valid referenceable variables in an aggregate filter
You can only reference a variable in an aggregate filter that is a formula. A formula expression can contain Boolean logic. For an aggregate filter, it is not possible to evaluate a variable in the formula expression as the name of a filterable column or as a filter operator.
There are multiple factors that govern which types of variables you can reference. These factors are as follows:
- The context of the aggregate filter: insight, Incorta Analyzer Table, or Incorta Analyzer View
- The formula expression for a formula pill
Here are the types of variables that you can reference as a formula expression in an aggregate filter:
- date system varaible
- external session variable
- internal session variable
- miscellaneous system variable
- presentation variable
Although it is possible to have a Login Name that is a numeric value, the $user
miscellaneous system variable is typically not a filter value for an filter expression for an aggregation filter formula.
The following table shows the matrix of referenceable variables and the respective context.
Referenceable Variables | Context: Incorta Analyzer Table | Context: Incorta Analyzer View | Context: Insight |
---|---|---|---|
date system varaible | ✔ | ✔ | ✔ |
external session variable | ✔ | ✔ | ✔ |
internal session variable | ✔ | ✔ | ✔ |
miscellaneous system variable | ✔ | ✔ | ✔ |
presentation variable | ✔ |
Certain variables such as an internal session variables, external session variable, and miscellaneous system variables evaluate as a string. When a variable is a filter value, you must use a conversion function within the formula expression to convert variable to the appropriate data type.
View access rights
In order to reference a column or variable in an aggregate filter, you must have View access rights to the grandparent object (physical schema or business schema) or the variable.