In GoodData, it’s common for users to constrain a report’s data with a dashboard filter, so that certain categories of data are omitted, while others are included in a report’s computations. But what if you wanted to allow users to compare a metric’s values when all categories are included with its values when some categories are filtered out – and what if the user could use a dashboard filter to dynamically control the categories filtered out at any given time? In this article, we’ll explore this interesting use case by weighing the merits of three possible solutions for tackling the objectives outlined above.
Imagine you want to compare a metric between filtered to a specific segments to the value across all segments - and all that in a single report. For example, you may want to visualize the trend in a line chart with one line representing selected segment and another one for the whole base.
And of course, the user must be able to select the segment dynamically using a dashboard filter.
For example, you may track the average daily number of leads coming from various marketing campaigns, which naturally translates to the following logical data model:
Solution 1: WITHOUT PARENT FILTER
The requirements may seem to imply that we need two metrics: Avg Daily Leads filterable by the Campaign filter and a similar metric which is not filterable. We’ll define our two metrics as follows:
Average # Leads
Average # Leads (All Records)
WITHOUT PARENT FILTER clause effectively shields our metric from all filters defined on dashboards, reports or outer metrics. It solves our problem but it has an undesired side effect - our dashboard may include other filters (typically a date filter) that are expected to affect both “part” and “whole” metrics - if you select to filter the period of last 30 days, you are interested about the campaign contribution to all leads created during the last 30 days rather than to all leads at all.
Solution 2: Variables
There are three kinds of filter drop downs that can be put on a GoodData dashboard: attribute filters, date filters and variable filters. Most commonly we only use attribute and date filters, but variable filters can be very helpful in some specific situations.
GoodData’s “filtered” variables are really just duplicates of other project attributes that pre-filtered for a subset of the attribute’s values. In other words, filtered variables are placeholders for the expression: “Attribute IN (Value1, Value2, …).” (GoodData also supports numeric variables, but they are not relevant in this case). But it’s also possible to create a variable to serve as a duplicate of some attribute without actually filtering out any of its values. In fact, the default setting is not to filter any values.
Once you’ve created a filtered variable, you can use it to help define reports (through variable filters) and metrics like the following:
Keep in mind, when Campaign_Variable is defined in such a way that no attribute values are filtered out, the metric above will compute identical results to the SELECT AVG(# Leads) metric. But with a Campaign_Variable dashboard filter, users will be able to redefine Campaign_Variable on the fly, effectively manipulating the variable metric while the original metric remains unchanged.
While variables allow us to meet all of our original objectives, they do have at least one shortcoming. While attribute dashboard filters can be interconnected in cascading parent/child relationships, this is not supported for variable filters. If cascading dashboard filters is a priority in your project, you may need to turn to another solution.
Solution 3: Connecting campaigns indirectly
The last solution may look like it was designed by a mad data scientist (in fact, it was). But it’s actually just a rather unusual application of the cross data set filtering with filter injection. It’s based on the idea of injecting a filter (link) using a COUNT submetric that is based on an association data set (a.k.a. faceless fact table) connecting our main data set (Daily Leads) and the Campaign attribute.
It means it requires a model change:
What, Campaign is no longer referenced by Daily Leads? But that means we lose the advantage of Daily Leads metrics being automatically filtered by a Campaign dashboard filter! Well, that’s actually what we want to happen.
Our “part” metric now looks like this:
The trick is that the Campaign filter affects the
COUNT sub-metric, the
BY Daily Leads, ALL OTHER part will ensure that the
COUNT sub-metric is computed for every record of Daily Leads and the “greater than zero” condition will pick only the Daily Leads records such that they are connected to the selected Campaign through at least one record in the association table.
If we prefer to keep the Campaign available for usual slicing and filtering rather than moving it to an associated dataset, we can keep the model as it was and add a duplicate attribute that is used only by the dashboard filter. In that case, the data model would be as follows:
The definition of the “part” metric will stay the same.
A dashboard that compares a filter based selection to an unfiltered value (or even multiple filter selections between each other) can be done easily with variables as they allow us to apply the filtering rule directly on a specific part of a metric rather than filtering the entire metric.
We can produce the same results by connecting the filtering attribute to the main data set indirectly via an association table and applying the filter to a COUNT sub metric.
The most trivial version is adding the “WITHOUT PARENT FILTER” clause to the “whole” metric. However, it’s not practical as it eliminate all filters including the date filters that we usually want to apply to the “whole” metric too.