MAQL With/Without Parent Filter

  • 29 March 2021
  • 0 replies
  • 15 views

Userlevel 1

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.

kl3IpqhRyB6-pobLCbO1cD54pN8bxjpnXIntpB1mSeeDKJ6CQrUiVrzsLmE0Qs5l-lZI0EHbayPjQoI7Rc_wLFeDT_8A9F3Ptjuaehz8Bxlnxl3hXdCzGZzaoG9DnL56zxxA-nac

ja8Eo0fftYOKGbVD2dDjc8qhLBJTYGb3AEd0-JHgI9t7xsazEb8xT7XoG8ufm3SiLMTTe_sNMCIUbdw1EJB8bdnCayKpTGdbyCtwjawfDBz84t8wHGIe35ux2elqDCLxPDA9O5wQ

Filter conditions can also be added to individual Insights when designing in Analytical Designer.

KAoZ3XAk5aesYp5Fz14VOThn_8HJGUQr6kgtgEWtAmAzKWIP50AsfO2h7336m3LGTUpnQi3nTKaCMy9xyGz1HrO4gBkekQzDO7s1cIiGS-6WcMfabQ8zS6TGC3_3fiV3nfIzf0Hp

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

XHc3tW97zpOzJ4lTXnr3CnW8lfHPo1Po6c8jM5ySk2bciWpB9oFAddcz2P-ZOyUYsqYJjsl-Fkkf3aj1mKTsAVrNEFQGl_ccHwXEoKmoSz0xdkT9gmuni_X2UzBIizZuMQHmGnI7

X0RfUyrHXppBe8OPr-NQjow4TXQnNbmi0KX-GIBpU5EHoVe_yCte_d15VQPJv_8YKquycwpeawXkari83vl7hana400EZCCRezLyBy5ap45vTjGoEhq5jV5zZu1OJSBJp7gcKR09

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)

zf72J8VbEC19pxSdOgXQXS8ZLfhaIlZN_cKtnNMBDQo-KXToHvM53qvmOPy4_ebhD_Hcvq-eldF7PFrEJ9zZCNzkLXK3N9SUSgt6mCCCnaldiLCzOJsU88ZiOesjDDmW3skW9L9P

gwnDUFeM7L-yotJRjvyCyNkY0UY0h1WXxSeD65lbrYf3FlJn814tYmBD7SVrbeXILaPdwY00WZ9QcVPMxiEla0GFf8ap_X7E7FSM-YR0pFEBYw0svxo7lfKrCwtSoCxQaYL3UMuL

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))

hB2VPIAWIjWX5KniaO-J_r3i93e_Pt2GEv0tMKKKuiBYNJZqBuuRHH295Zi6-rXIXN3LMNsje6YWGgP8F-6pYrwj2IRRrGQP3qEQ5dw5s_jfrDeysWAJv7_mgXcRZtbT-0vHqPKH

O0L4Bk4F9hqqsXHj7z3MoGtjN-BnwWSLJZX5amJTLij1ty9V-KWGuGniukXyc8Q1vT1iwu7oHzQZd2ObeUlL4EfR0bC8phUGnwnlgHeJ-8kK10iQlKa9n3HAuG4W9-jbDhHJlYD_

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.

  1. SELECT MAX(SELECT Total Sales BY Product ID WITHOUT PF EXCEPT Date (Date)) BY Product Category, ALL OTHER
    wRjtRB2tCEudlPTFEtKcZ6tjgW9hpPcUez7SGagg3JEAaIlqBJmREqQd0RyTH_VK-T5qcqqDRzIuFP1zdopLGsG7fy6wyyivezmPrfJa2qFvEMhbR-YvQSb8ZkXVjC76gxpydASc
    LM4OH3x73DsBnBkiw-Aqec9CB2FPKclUkh3wqInmo9S_jng08jP0fsD_nel8sgiXmQ9vjrkuLqKMmrhlCTcNOKxSQW2Bg58-ghdnXi-G4OE3W9gWDDQffCEvfLVya4XUGEFmD2gP


    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”.
     


  2. SELECT MAX(SELECT Total Sales BY Product ID) BY Product Category ALL OTHER WITHOUT PF EXCEPT Date (Date), Product Category

    cGfGL_uRpovZcGneWB8SJ_H4sfbFygSIBcQ5N23oBptjDtT_qSfxfOLXJxpHArmfGoSVdVcVTvFNd1aKBN9l1zAzQk6lCK_Ay4CFH8rmhzEImUaq-QiXqiH_TNu6LPu6shOqxphC
    -5BtGuLiar554boYg4C7D9Fc7u_5IyhBuGejNxc7ZtXYTuVpKWjWJbyeV3d8tUZmD5asXYC7r0PsLj3OGFE0hQcxFknqpEmVF7vT3H2JdSGCb5wdqBuaFwXMABOa0uzxx8qf79rm


    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.


0 replies

Be the first to reply!

Reply