Custom reports

Interactive comparison of selected values from Google Looker Studio series charts

Interactive comparison of selected values from Looker Studio series charts

Keeping charts as simple as possible is very challenging. For some cases, a single interactive feature can offer a first level of data exploration or calculation without overloading the default state of your Looker Studio (formerly Data Studio) chart.

In this article, I will describe an home made custom solution to give a way for readers to quickly calculate the difference between 2 selected values, available in a series charts. Quite simple & efficient, displaying the big picture by default, but also an option to compare values with absolute and relative difference to help for deviation or comparison first explorations.


The result

We can apply this tip for any nature of data set, composed at least with one dimension & one metric at least, and displayed through a Looker Studio chart where we can select 2 values (line, bar chart, tables…). Let’s see the result in 2 chart examples:

  • Trend of Daily sales displayed inside a line chart: We often need to calculate the difference between 2 specific dots of a trend line, because there a specific pattern (weekly trend here) or because we wish getting more info about a detected anomaly (deviation)
  • Sessions by country with a bar chart: Beyond a top of values, it is quite nice to compare dynamically 2 specific countries

As you will see, I’ve included an option to decide which value (higher or lower) is used as the reference for difference calculation. I consider this option very important as a simple way to cover most of kinds of usage.




Ingredients

Now, we will focus on the daily sales chart to go through the tutorial. Don’t worry, the other example is very similar and easy to build you understand the next chapters.

For my case, data are sourced from Google Analytics (GA) to inject more challenging aggregation constraints (fun is not a straight line), and above all, to build a recipe compatible with data sources connector with auto aggregated metrics (Google Analytics, Google Search Console…).

So , to build this chart, we need the following elements:

  • A parameter created at the data source level to select between higher and lower value as reference for difference calculation (in the example, GA data source) ; with the related drop down list control on the page
  • A calculated field at the data source level to relay the value of the parameter to the blended data below
  • A single-table blended data to isolate our data set and unaggregate metrics provided by GA
  • A line chart to display our filtrable trend
  • 2 table charts for relative difference and absolute difference, using blended data and chart-level calculated fields

1. Parameter

Reference

Create the parameter “Reference” (here with “comparison_reference” as id) as a number data type with a permitted list of values, filled with the following values and labels :

  • 0 value for “Lower value” label (default value)
  • 1 value for “Higher value” label

Parameter configuration to select the value used as reference for the calculation of difference

Then, add the parameter in the report page as a drop-down control, turning off the search box and adapting the other style options.


2. Calculated field

Parameters are not available directly inside blended data. Let’s create a dimension to work around this limitation and use the value selected on the parameter inside required data blending.

It is just a reference to the parameter. A way to get the current value.


Lower or higher as reference
Reference


3. Data blending

Create a single-table data blending with your dimension, your metric and “Lower or higher as reference” numeric dimension as the 2nd metric.

As described above, we have nothing to blend. We just need to isolate our data set and unaggregate sales metric by day from GA data source. In other words, it is a way to keep sales by day and manipulate these data rows inside each Looker Studio chart sourced by blended data.

In my example, data blending of Looker Studio is configured with the following fields and named “BD – Daily sales”:

  • Dimension : Date
  • Metrics
    • Sales
    • Lower or higher as reference

Data blending to isolate chart data set

4. Line chart configuration

It’s time to create our line chart displaying daily sales. Here the important features of configuration. Feel free to adapt the other styles options.

  • Chart type: Line chart (and not time series chart, multi selection is not available on it)
  • Setup tab
    • Data source: “BD – Daily sales”
    • Dimension: Date
    • Metric: Sales
    • Sort : Date Ascending
    • Chart interactions
      • Turn on cross filtering
      • Turn off change sorting
  • Style tab
    • Series #1 displayed as a line, showing points

5. Table charts configuration

Now, add 2 table charts with the following common options.

  • Setup tab
    • Data source: “BD – Daily sales”
    • Dimension: the calculated field “% Diff” for the first table and “Diff” for the second one (see formulas below)
    • Row per page: 1
    • Turn off cross filtering
  • Style tab
    • Don’t show header
    • Table body: Hide row numbers
    • Missing data: Show “” (blank)
    • Chart header: Do not show
    • …prefer removing all other elements of the chart to not overload the chart area when comparison selections is not applied

% Diff
IF(COUNT_DISTINCT(Date) = 2,
     CONCAT(
         IF(MAX(Lower or higher as reference) = 0, "+","")
         ,
         CAST(ROUND(
             IF(MAX(Lower or higher as reference) = 0, (MAX(Sales)-MIN(Sales))/MIN(Sales), (MIN(Sales)-MAX(Sales))/MAX(Sales))
         *100,0) AS TEXT)
     ,"%")
 , NULL)

Diff
IF(COUNT_DISTINCT(Date) = 2,
     CONCAT(
         "Abs diff: "
         ,
         CAST(
             IF(MAX(Lower or higher as reference) = 0,(MAX(Sales)-MIN(Sales)) ,(MIN(Sales)-MAX(Sales)))
         AS TEXT)
     )
 , NULL)

6. Text elements

At last, add some explanations to help readers to use the feature. It my example, I added a call-to-action with a small icon and also, in the footer, how to make multi selection on Mac and Windows.


7. Configuration review

As usual, let’s make a review of the full configuration with a small video.



Voilà, c’est fini

I hope you’ve enjoyed the implementation of my new Looker Studio recipe and are satisfied by the final render of this custom feature, a nice way to reveal complementary and interactive data.