Creating Measures that Override Filters in GoodData.CN

  • 11 June 2021
  • 0 replies
  • 116 views

Userlevel 1

When creating measures and designing dashboards in GoodData.CN, there may be times where you want a calculated value to remain the same after applying a dashboard filter. This is possible by creating measures with the WITHOUT PARENT FILTER keyword in MAQL. 

 

This keyword prevents any so-called Parent Filters from applying towards the measure. A Parent Filter is a filter that is applied to an object that uses the measure, such as an insight or dashboard. 

 

The keyword can take the following forms: 

SELECT … WITHOUT PARENT FILTER

SELECT … WITHOUT PARENT FILTER EXCEPT …

SELECT … WITH PARENT FILTER

SELECT … WITH PARENT FILTER EXCEPT …

 

In the MAQL syntax, “PARENT FILTER” can also be abbreviated as “PF”. So, “WITHOUT PARENT FILTER” can also be written as “WITHOUT PF”. 

 

In this article, I will lay out a few common examples of when you might use this feature. You can read more in our documentation.

 

Common use cases

One use case is to have a dashboard with filterable insights where one or more insights remain static when filters are applied. This way, the dashboard can showcase a comparison against the whole even when detailed filters are selected.

 

3Uvfl-8LwN7N9eaV75V6OX1V42XODXrPF1G_wvByQcgXqpB5WK_dQrIeBqCdM2wJcRpsnr-_olei4Ncr7wAzKWDdr5jBQW2Q0JWIq-vnNATrnNe2RlNX0oZ698G1f1zrrJG_vKYN

Creating measures that utilize WITHOUT PARENT FILTER allows the four overview insights at the top of this dashboard remain the same when filters are applied, while all other insights update according to the filters. 

 

Another use case is to have more complex measures that calculate a value based on some total, unfiltered value. Such as a measure that shows the total revenue made from the current filter selections relative to the total unfiltered revenue. 

 

Below are a few examples of how to create these types of measures in your workspace. 

 

Calculating total values that remain unfiltered

A company that sells a variety of products may have a dashboard that can be filtered by product or product category but may also want to display the total sales regardless of the product filter applied. They could create a measure that does this utilizing WITHOUT PARENT FILTER as follows:

SELECT SUM({fact/sale_records.sale_value} WITHOUT PF

 

In the demo data provided with the Community Edition of GoodData.CN, we can create a measure for this same use case: 

SELECT SUM({fact/order_lines.price}*{fact/order_lines.quantity}) WHERE {label/order_lines.order_status} = \"Delivered\" WITHOUT PF

 

This measure would calculate the total revenue for the orders that were delivered and the value will not change if any filters are applied from the insight or dashboard where the measure is used. This measure could be useful in a variety of situations. 

 

For example, we may have a measure, named {metric/revenue}, which calculates the revenue and does not use “WITHOUT FILTER” as follows: 

SELECT SUM({fact/order_lines.price}*{fact/order_lines.quantity}) WHERE {label/order_lines.order_status} = \"Delivered\"

 

We can create a second measure, {metric/revenue-without-filters}, that uses the previous measure but ignores the parent filters:

SELECT {metric/revenue} WITHOUT PF

 

We can use this second measure to display the total, unfiltered revenue, and we can nest it in other measures to calculate values relative to the unfiltered revenue. 

 

For example, we can create a third measure that calculates the ratio between the two:
SELECT {metric/revenue} / {metric/revenue-without-filters} 

 

This third measure can be used in an insight on a dashboard to show what percentage of the company’s total revenue was made from the current filter selection:

7eoorfNmoZkdEhk1WNVRkqCgHTBZO8UmcQ-jae_-y4_gQzpnos_67zfeYAX7oRktWGfLKTEyQ2QwSdIhCTcP0CD83vxjrQkYKHCYd-vUg7nkdrKKj3AYHJJcIzO36hnxxiq7BbAZ

 

We could also create a measure that shows the percentage of our total revenue comes from a certain product category:

SELECT {metric/revenue-electronics-without-filters} / {metric/revenue-without-filters} 

 

For another example, we can use the following measure to give us a total count of all customers, irrespective of the filters applied: 

SELECT COUNT({attribute/customers.customer_id}, {attribute/order_lines.order_line_id}) WITHOUT PF

 

Creating a measure that respects some but not all filters

In the demo data, we can create measures that calculate the total revenue from each product category that won’t update when we apply a dashboard filter:

SELECT {metric/revenue} WHERE {label/products.category} = \"Electronics\" WITHOUT PF

 

If we create a measure like this for each product category, we can display the total revenue for each category on the dashboard that will remain after filters are applied:

r7zg9LNC5G5Y7JKcHd878RQozkqcTwd9eQ2DU55lPGX8MMfBidjS73nQi1Qgj-x2Fsh2Ss9nMSol_dSxLW7G88F5wYYgtLyJ937lKten3QgnZ0r-56-dQwIyphHH7108E_nBOy2w

 

These measures will remain the same after any filters are applied, but we may want some filters to still update the values displayed, such as the date filter. 

 

By appending the “EXCEPT” keyword after WITHOUT PF, we can create a measure that respects only the filters listed after “EXCEPT”. Similarly, we can use “WITH PF EXCEPT” to respect all filters, excluding ones listed after the “EXCEPT” keyword. This allows for a more complex logic. 

 

By changing the measure to the following, we can have its values update with the date filter, but not with the category or product filters:

SELECT {metric/revenue} WHERE {label/products.category} = \"Electronics\" WITHOUT PF EXCEPT {label/date.day}

 

UkZDl5qRRCZu3z12Ovt4yvZN8tVcxRbnS0i9ZfTPcEGO4A9n-XsMWOwPX2FPrdk6d9nEg2jls5Q7SBRorXbp-0VGV5Ik7Xsox-_MFQbWf508PfTsGCsw7exaBm5R3hjTMbvVMR40

The insights now update according to all filters, except the “Category” and “Product name” filters. 

 

In this specific example, we could also rewrite this measure as follows: 

SELECT {metric/revenue} WHERE {label/products.category} = \"Electronics\" WITH PF EXCEPT {label/products.category}, {label/products.product_name}

However, be aware that if you were to later edit the dashboard and add new filters, then they would apply to the measures in this second version since the newly added filters would not be explicitly mentioned. To prevent unwanted outcomes, be sure to name your measures something descriptive of all its attributes, including which filters are included or excluded from its definition.   

 

As another example, we may have a sales dashboard that can be filtered by date or product, and we can use “EXCEPT” for the date label to create a measure that calculates the total historic sales for whichever product filters are applied:

SELECT SUM({metric/revenue}) WHERE {label/date.year} < THIS(YEAR) WITH PF EXCEPT {label/date.day}

 

This measure calculates the total revenue for all sales from all dates prior to the current year. Its value will be filtered by any non-date dashboard or insight filters, such as product name or category, and applying a date filter will not change its value.

XNwNCmH8Hi5cUgbXNOb02HwlOEw-NLabxN8iWb2uTf33QtTHw-APRudOfLm0dH3tIVA5fErqJHb5DBJDcLtx6cZhZaghj4sCxCg7G6qUc8e28oanrrH6Kec4hESEBFUR1jWkPPNz

 

In conclusion this should hopefully give you an introduction to the WITH / WITHOUT PARENT FILTER keyword and demonstrate some of its usefulness. For more details on overriding parent filters, you can check out our documentation.

 

If you are interested in GoodData.CN, please contact us.

Alternatively, test a trial version of GoodData Cloud:

Start Your GoodData Analytics Trial — Commitment-Free

 


0 replies

Be the first to reply!

Reply