Compare the Same Measure with Different Filters

Recently I was asked “Does one of your Tableau CRM blog posts cover how to use a compare table to drive a drop down KPI list on a chart?”. Short answer to that was “No”. But I figured let’s change that answer to “Yes”.

In this blog, I will show how you can have a compare table with the same measure twice, but using different filters. I will then make the filters dynamic based on two list selectors. The end result should look something like below.

You are seeing opportunity amount grouped by opportunity owner, the amount is filtered by forecast category – each of the two amount measures are filtered by the selection from the list selector.

Creating List Selectors

I’ve created a new blank dashboard for this use case. The first step is to add two list selectors from the toolbar on the left side.

Next, I click inside the first list selector, pick the dataset that I’ll be using. I’ve just picked a random opportunity dataset. Once selected you can pick the field you want to be used for the filter – in my case Forecast Category.

Once the list selector is populated you need to make sure you do not broadcast the selection, because we want to control this with a binding instead. This is done by selecting the widget and switch to the Query tab in the right panel and uncheck the “Broadcast selections as facet” checkbox. Note I am going to allow my users to select multiple values from the list.

Finally, you, of course, need to repeat the steps for the second list selector.

Creating the Compare Table

Alright let’s create our pyramid chart with our opportunity owner and filtered amount measures.

Click the create query in the top right to get started and pick the same dataset as before. Make sure to switch to a compare table so we can add the same measure twice by selecting the table option in the top right corner.

Next, we will add the grouping of Opportunity Owner and the measure Amount twice. We will also add a filter for each of the measures by clicking the arrow next to the measure and select “Add Filter”. I’ve chosen the Forecast Category, which we are using in the list selector. You can pick any random value, it doesn’t matter what as we will be replacing it with binding later, we just need the filter syntax.

Now we just need to give our query a name and switch to the chart type we want. I’ve chosen a pyramid chart. Once you are happy click “Done” and drag the query to the canvas.

Making it Dynamic

Now let’s look at how we can make this dynamic with bindings. We want to have one side of the pyramid filtered by the first list selector and likewise for the other side of the pyramid just with the second list selector.

Note: You can learn more about bindings and how to use them here.

To simplify the binding creation let’s use the advanced editor. Click on the pyramid chart widget and hit the advanced editor button at the top of the panel to the right.

In the advanced editor make the following selections for the first binding:

• Data Query: First list widget – in my case ForecastCategory_1
• Interaction Type: Selection – the user are expected to make selections for the binding to trigger
• Data Serialization Function: asObject() – because we are allowing for multiple selections we can expect an array of values.
• Data Source
  • Data Selection: Column – because we are allowing for multiple selections
  • Column: dimension – in my case ForecastCategoryName

The binding now looks like this:

{{column(ForecastCategory_1.selection, [\"ForecastCategoryName\"]).asObject()}}

You can copy this binding and in the Query tab find the query where it says filter.

"filters": [
             [
               "ForecastCategoryName",
               [
                 "Commit"
               ],
               "in"
             ]
            ]

You want to replace the [“Commit”] with the binding of course remembering the double quotes around the binding.

"filters": [
             [
               "ForecastCategoryName",
               "{{column(ForecastCategory_1.selection, [\"ForecastCategoryName\"]).asObject()}}",
               "in"
             ]
            ]

We will have to repeat this step for the second filter only the binding will be referencing the second list selector instead. In the editor make the following selections for the second binding:

• Data Query: First list widget – in my case ForecastCategory_2
• Interaction Type: Selection – the user are expected to make selections for the binding to trigger
• Data Serialization Function: asObject() – because we are allowing for multiple selections we can expect an array of values.
• Data Source
  • Data Selection: Column – because we are allowing for multiple selections
  • Column: dimension – in my case ForecastCategoryName

You should now end up with a binding that looks similar to this:

{{column(ForecastCategory_2.selection, [\"ForecastCategoryName\"]).asObject()}}

Find the second filter in the query and replace it with the binding.

And that’s it. You can now save your changes. First those that you made in the advanced editor and then the dashboard. Thereafter go ahead and try out your comparison chart and make different selections in the list selectors.

What if it’s a SAQL Query?

Let’s say for some reason you have built your query in SAQL the binding will look different. If you are unfamiliar with SAQL, I have a collection of blogs to simplify this concept here. In this case, you could have used the data serialization .asEquality() for your filter binding. I’ve done a whole blog on data serialization for filters using SAQL here.

However, I figured you might want to see how the use case is done in SAQL, so below you can see how this same query would look in SAQL including the .asEquality binding.

l = load "opportunity1";
r = load "opportunity1";
l = filter l by {{column(ForecastCategory_1.selection, ["ForecastCategoryName"]).asEquality('ForecastCategoryName')}};
r = filter r by {{column(ForecastCategory_2.selection, ["ForecastCategoryName"]).asEquality('ForecastCategoryName')}};
q = group l by 'Owner.Name' full, r by 'Owner.Name';
q = foreach q generate coalesce(l.'Owner.Name', r.'Owner.Name') as 'Owner.Name', sum(l.'Amount') as 'sum_Amount Left', sum(r.'Amount') as 'sum_Amount Right';
q = order q by 'Owner.Name' asc;
q = limit q 2000;

Written by

Rikke Hovgaard

I have worked with CRM Analytics since 2015 (back then Wave). I started this blog to help make the journey to master CRM Analytics easy by documenting tips, tricks & cool solutions.

See author's posts