PARENT Filters

A parent filter is any data filter applied to a workspace object from one of its parent objects. A parent object can be considered any insight that contains the metric or another metric built on the metric.

PARENT Keywords

MAQL supports a set of keywords that can be used to override and control which parent-level filters are applied to a specified metric. When inserted into your metric definition, these keywords specify whether to apply any filters from the metric’s parent metric, insight, or dashboard to the data used in the metric calculation.

You can include or exclude all filters or specify the specific filters to include or exclude.

These keywords are applied to selecting data before it has been aggregated in the metric computation. For more information on applying filtering to data after it has been aggregated, see Filtering with the HAVING Clause

The effects of these keywords are best demonstrated by example. Suppose you have the following metric (M~1~), which has insight-level filters applied to it:

Definition

Insight Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

In the above table, you can see the value generated for the metric (M~1~) is 500 when no filtration is applied. When the insight filter (F~1~) is applied, the value for the metric is 350. This example can be used to describe the effects of each of the following parent keywords.

If your metric contains multiple keywords, they must be listed in alphabetical order:

  SELECT (...) (BY ...) (FOR ...) (WHERE ...|HAVING ...) (WITH PF|WITH PF EXCEPT|WITHOUT PF|WITHOUT PF EXCEPT)

WITHOUT PARENT FILTER

Description

Removes parent filters from being applied to the metric. All parent filters specified in insights, dashboards, and any metrics in which this metric is nested are ignored.

Any user filters in the workspace are never ignored. They are always applied even if parent filters are ignored in individual metrics. See Filter Expressions .

This clause can be shortened to WITHOUT PF.

Example

In the table below, you can see how the WITHOUT PARENT FILTER clause affects the filtered version of the insight.

Definition

Insight Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT M1 WITHOUT PARENT FILTER

F1: Group = X

500

Since the metric in the third row removes the parent filter in the insight, the returned value matches the calculation when no insight filter is applied in the first row.

Syntax

SELECT ... WITHOUT PARENT FILTER
SELECT ... WITHOUT PF

WITHOUT PARENT FILTER EXCEPT

Description

Remove parent filters, except for any filters specified as a comma-separated list of attributes.

This clause can be shortened to WITHOUT PF EXCEPT.

In EXCEPT clauses, multiple attributes may be specified as a series of comma-separated values.

Excepted attribute filters are applied to the specified attribute and to all attributes of coarser granularity in the hierarchy. For example, in the hierarchy State-County-City, the values of the attribute City do not group the values of County, but County groups City because County has coarser granularity than City. So, if City is excepted, County and State are excepted too. If you apply the exception to the State attribute, it is the only attribute filter that is excepted.

Example 1

In the table below, you can see how the WITHOUT PARENT FILTER EXCEPT keyword works with the State-County-City hierarchy.

Definition

Insight Filter

Metric Value

SELECT M1

(none)

1000

SELECT M1

F1: County = X

600

SELECT M1 WITHOUT PF

F1: County = X

1000

SELECT M1 WITHOUT PF EXCEPT City

F1: County = X

600

SELECT M1 WITHOUT PF EXCEPT State

F1: County = X

1000

SELECT M1 WITHOUT PF EXCEPT State

F1: State = Y

550

  • In the above example in the third row, the WITHOUT PARENT FILTER removes all parent filters from the metric, so COUNTY = X is not applied. The returned results match the unfiltered version of the metric in the first row.
  • In the fourth row, City is excepted. Since County has coarser grain than City, the parent insight filter (County = X) is applied.
  • In the fifth row, the excepted filter is State. However, the insight filter is at the County attribute level, which is finer than State. Therefore, the filter is not applied, and the results match the unfiltered version in the first row.
  • In the fifth row, the excepted filter is State, which matches the insight filter. So, the results are filtered.

Example 2

In the table below, you can see how the WITHOUT PARENT FILTER EXCEPT keyword affects the filtered version of the insight.

Definition

Insight Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT M1

F1: Group = X;F2: Priority is Y

250

SELECT M1 WITHOUT PARENT FILTER EXCEPT Group

F1: Group = X;F2: Priority is Y

350

In the above example in the fourth row, the WITHOUT PARENT FILTER EXCEPT removes all parent filters from the metric, except for one: Group, so the Priority filter is not applied to the metric. The generated metric value equates to the value generated in the version in the second row.

Syntax

SELECT ... WITHOUT PARENT FILTER EXCEPT Attribute1,...
SELECT ... WITHOUT PF EXCEPT Attribute1,...

 

Example 3

Syntax

DefinitionInsight Filter

SELECT (SELECT M1 WITHOUT PF EXCEPT Date) WHERE (SELECT M2 BY Date) > Y

Group = X
  • M2 is filtered by group.
  • M1 is not filtered by group, only by the numeric range filter in the WHERE part.

 

Example 4

Syntax

DefinitionInsight Filter

SELECT (SELECT M1 WITHOUT PF EXCEPT Date) WHERE (SELECT M2 BY Date) > Y

Group = X Date = Z
  • M2 is filtered by group, date.
  • M1 is filtered by date and the numeric range in the WHERE part.

WITH PARENT FILTER

Description

Applies all parent filters to the metric. All parent filters specified in insights, dashboards, and any metrics in which this metric is nested are applied to the calculation.

This clause can be shortened to WITH PF.

Example

In the table below, you can see how the WITH PARENT FILTER clause affects the filtered version of the insight.

Definition

Insight Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT (SELECT M1 WITH PARENT FILTER) WHERE Group = Y

F1: Group = X

null

In the third row above, the definition includes a nested metric in which parent filters are forced to be included from inside the nested metric.

  • In the parent metric is the filter Group = Y.
  • At the insight level, the filter is Group = X.
  • The WITH PARENT FILTER keyword means that all parent filters are applied to the metric. During evaluation, both Group = X and Group = Y are applied to the data as a logical AND. Since a data element may be associated with a single attribute value, there are no data elements that match both filters, and the result set is null.

Syntax

SELECT ... WITH PARENT FILTER
SELECT ... WITH PF

WITH PARENT FILTER EXCEPT

Description

Includes all parent filters, except for any filters specified as a comma-separated list of attributes.

This clause can be shortened to WITH PF EXCEPT.

In EXCEPT clauses, multiple attributes can be specified as a series of comma-separated values.

Example

In the table below, you can see how the WITH PARENT FILTER EXCEPT keyword affects the filtered version of the insight.

Definition

Insight Filter

Metric Value

SELECT M1

(none)

500

SELECT M1

F1: Group = X

350

SELECT M1

F1: Group = X;F2: Priority is Y

250

SELECT M1 WITH PARENT FILTER EXCEPT Priority

F1: Group = X;F2: Priority is Y

350

In the above example in the fourth row, the WITH PARENT FILTER EXCEPT includes all parent filters in the metric, except for one: Priority, so in this case, the Priority filter is not applied to the metric. The generated metric value equates to the value generated in the version in the second row.

Syntax

SELECT ... WITH PARENT FILTER EXCEPT Attribute1,...
SELECT ... WITH PF EXCEPT Attribute1,...