Parent filters (PF) refer to filters applied to a metric from the Dashboard or Insight. 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.
Filter conditions can also be added to individual Insights when designing in Analytical Designer.
With/Without Parent Filter
The MAQL Metric editor offers an option to override the application of some or all of the parent filters (PF) to a specific metric. Here’s a quick guide:
-
WITH PF - All parent filters are applicable to this metric. This is the default setting, and rarely needs to be specified.
-
WITH PF EXCEPT Filter 1,Filter 2,…,etc. - All parent filters are applicable, except for Filter 1 (F1), Filter 2 (F2),…, etc. This option allows you to selectively prevent certain filters from affecting a metric.
-
WITHOUT PF - No parent filters are applicable to this metric. Similarly, numeric range filters, which are applied after a metric’s calculation is complete, will not affect the calculation of a metric where WITHOUT PF has been applied. However, the range filter can filter out the appearance of the metric if it does not fall within the specified range.
-
WITHOUT PF 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.
For more in depth information on Parent Filter overrides, please check out 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, the denominator Total Sales metric has the “BY ALL OTHER WITHOUT PF” clauses which ensure the denominator value is the overall Total Sales value will not be affected by any parent filters, as depicted above. The Product category and Date range filters only affect 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, product attribute 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. However, the WITHOUT PF clause will protect against effects from non-product attribute filters, such as “Delivery State”.
-
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.
