When working with GoodData, users often 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.
This article discusses the scenario in which you want to allow users to compare a metric's values when all categories are included with its values and some categories are filtered out, and the user wants to use a dashboard filter to dynamically control the categories filtered out at any given time.
Imagine that 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 want to visualize the trend in a line chart with one line representing selected segment and another one for the whole base.
In this scenario, 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 will 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 the 'part' and the '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 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.
After you 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:
As you can see, Campaign is no longer referenced by Daily Leads. This measn we lose the advantage of Daily Leads metrics being automatically filtered by a Campaign dashboard filter - this is the intended behavior.
Our 'partial' 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 is not practical as it eliminate all filters including the date filters that we usually want to apply to the 'whole' metric too.