MAQL With/Without Parent Filter
Parent filters (PF) refer to filters applied to a metric from the Dashboard:
When designing a dashboard, users can add dashboard-level parent filters that will then filter the data for all insights on that dashboard. However, depending on how your LDM is built, these filters may show visible effects on some KPIs and have no such effect on others.
With/Without Parent Filter
The MAQL Metric editor offers an option to override the application of some or all of the parent filters to a specific metric. Here’s a quick guide:
- WITH Parent Filter - All parent filters are applicable to this metric. This is the default setting, and rarely needs to be specified.
- WITH Parent Filter EXCEPT F1,F2,…,etc. - All parent filters are applicable, except for parent filters F1, F2, …, etc. This option allows you to selectively prevent certain filters from affecting a metric.
- WITHOUT Parent Filter - No parent filters are applicable to this metric.
- WITHOUT Parent Filter EXCEPT F1,F2,…,etc. - No parent filters are applicable to this metric, except for parent filters F1, F2, …, etc. This option allows you to selectively allow certain filters to affect a metric.
Note: Parent Filter can be shortened to PF
For more in-depth information on Parent Filter overrides, see this page.
Use Cases
Total Account Inquiry Calls:
SELECT COUNT(Call ID) WHERE Call Type = 'Account Inquiry' WITH PF EXCEPT Call Type
Call Centers use this metric to keep track of how many Account Inquiry Calls they receive. The “WITH PF EXCEPT Call Type” clause ensures that this metric will always show the true value, even if a Call Type parent filter is set to filter for a different type of call ie. Customer Support. Without the EXCEPT clause, the above metric would reduce to 0.
% of Total Sales:
SELECT Total Sales / (SELECT Total Sales BY ALL OTHER WITHOUT PF)
In this example, Total Sales is divided by itself. However, the denominator metric will not be affected by any parent filters, as depicted above. The Product category and Date range filters only affects the numerator Total Sales metric, which leads to the changing % of Total Sales.
% of Total Sales:
SELECT Total Sales / (SELECT Total Sales BY ALL OTHER WITHOUT PF EXCEPT Date (Date))
Expanding on the previous example, we have made the denominator filterable by parent filter “Date (Date)”. As a result, in the left-hand display, % of Total Sales = 1.00 because the filtered Total Sales numerator is being divided by an equivalent denominator. However, the right-hand display shows that while the “Date (Date)” parent filter may still affect the denominator metric, other parent filters like Product category do not.
Parent Filter Scope
Like other MAQL clauses, the position of the Parent Filter override clauses can change the scope of the override effects. To observe this, let’s look at the below example.
The goal of the following metric is to find the maximum revenue generated by any product within a “Product Category” and in a given time period. The two versions will depict how the scope of WITHOUT Parent Filter clause changes based on its location within the MAQL Statement.
SELECT MAX(SELECT Total Sales BY Product ID WITHOUT PF EXCEPT Date (Date)) BY Product Category ALL OTHER
In this version, WITHOUT PF is applied to the inner metric SELECT Total Sales. However, because the scope of the WITHOUT PF clause doesn’t extend to the outer “MAX()” aggregation, parent filters can still filter the data supplied to the outer metric. This filtered data is then inherited by the inner metric. So in the left-hand display, all products with Product Category = Electronics are being analyzed for the maximum Total Sales. In the right-hand display, only two products are being analyzed, which has led to a change in the observable maximum Total Sales.
SELECT MAX(SELECT Total Sales BY Product ID) BY Product Category ALL OTHER WITHOUT PF EXCEPT Date (Date), Product Category
In this version, WITHOUT PF is applied at the end and will lock out any filtering except on Date (Date) and Product Category. As a result, unlike in the previous version, when this metric is subjected to a parent filter on Product ID, the metric is unaffected.