Adding Advanced Period Over Period Tooltips to your Looker Dashboards

Since Looker announced the new measure type — Period Over Period (PoP) — a whole new world of data visualization possibilities and facilities has opened up. While this new feature simplifies and enhances comparative analysis, it also presents an opportunity to create more powerful and meaningful advanced tooltips which provides deeper insights to your dashboards.

For a comprehensive guide on PoP measures in Looker, refer to the official Google Looker documentation.

If you wish to create new PoP measures using LookML in your Looker projects, check out this article: New PoP Measure in Looker.

In this article we will explore how to effectively implement these tooltips using the ‘Period Over Period’ measure type in Looker, step-by-step:

Adding Tootlitps in Looker: A Step-by-Step Guide

You can create powerful and insightful visualization implementing the following tooltips:

The first step is to define your PoP measures. In this case, we’ll focus on a Month-over-Month (MoM) change measure:

measure: m_nb_users_mom_change {
    hidden: no
    description: "Month over month percentage change in users with arrows"
    label: "Users MoM % Change Δ"
    group_label: "1. Traffic"
    type: period_over_period
    based_on: sessions.m_nb_users
    based_on_time: sessions.date_date
    period: month
    kind: relative_change
    value_format_name: percent_1
}

Now, to update the metric and customize its appearance using Liquid, we add the html parameter to the measure definition:

measure: m_nb_users_mom_change {
    hidden: no
    description: "Month over month percentage change in users with arrows"
    label: "Users MoM % Change Δ"
    group_label: "1. Traffic"
    type: period_over_period
    based_on: sessions.m_nb_users
    based_on_time: sessions.date_date
    period: month
    kind: relative_change
    value_format_name: percent_1
    html: 
      {% if value == null %}
        null
      {% else %}
        <span style="color: {% if value >= 0 %}green{% else %}red{% endif %}">
          {% if value >= 0 %}
            &#9650;
          {% else %}
            &#9660;
          {% endif %}
          {{ value | times: 100 | round: 1 }}%
        </span>
      {% endif %} ;;
}

With this html section what we are doing is to define a conditional styling (green for positive, red for negative), adding ▲ or ▼ arrows based on the value and converting the value to a percentage.

Now you will be able to see your PoP metric in Looker like this:

Finally, to implement an advanced tooltip, we need to define another metric that combines the specific metric you want to show in your visualization with the percentage of change (the MoM measure you just created) of that metric over time:

measure: m_nb_users_with_mom_tooltip {
    hidden: no
    type: count
    sql: ${sessions.full_visitor_id} ;;
    description: "Number of users with MoM change in tooltip"
    label: "Users current + MoM % Change Δ"
    group_label: "1. Traffic"
    value_format_name: decimal_0
    html: {% assign date_field = sessions.date_date._value %}
          {{ sessions.m_nb_users._value }} | 
          {% if m_nb_users_mom_change._value == null %}
            null
          {% else %}
            <span style="color: {% if m_nb_users_mom_change._value >= 0 %}green{% else %}red{% endif %}">
              {% if m_nb_users_mom_change._value >= 0 %}
                &#9650;
              {% else %}
                &#9660;
              {% endif %}
              {{ m_nb_users_mom_change._value | times: 100 | round: 1 }}%
            </span>
          {% endif %} ;;
}

In this HTML section, we combine the session count (sessions.m_nb_users) and the MoM change measure (m_nb_users_mom_change), displaying the count value followed by a | symbol and the percentage change with conditional formatting.

Why use the count measure type?

In order to maintain consistency in Looker visualizations, we should use the same logic as the base measure. Since the original metric (m_nb_users) was calculated as a countof full_visitor_id , the tooltip metric must be defined accordingly, which is, as a count measure type. This approach ensures that the Liquid logic in the html parameter correctly pulls and formats the data as intended.

Find bellow the result:

Limitations:

• The date dimension must be included in the visualization for the PoP measure to function correctly.

• Parameters like period and kind are static and cannot be dynamically adjusted through filters.

• PoP measures aggregate data using timestamp types, while the base_on_time dimension (such as sessions.date_date) is often stored as a date type. When applying filters, this mismatch between date and timestamp data types can cause errors. Specifically, when the base_on_time filter is applied, it may lead to issues due to the dual filtering mechanism — one filter applies within the aggregation (CTE) and another in the main query. This results in errors when the data types differ between the two filters, such as a date being compared to a timestamp (for more information please check common issues reported in this article: Drive Decisions Faster: The New Era of Period-Over-Period Analysis in Looker).
  
  

Conclusion:

The introduction of Period Over Period (PoP) measures in Looker provides powerful comparative analysis capabilities, allowing users to track changes in metrics over time. By incorporating advanced tooltips, we can further enhance data visualizations, making them more insightful and actionable. Event if the limitation discussed above are important to consider, the value PoP measures and advanced tooltips bring to your data analysis is undeniable.

Thank you

If you’re interested in learning more about Looker, Business Intelligence, and other powerful tools to enhance your data analysis, stay tuned! We regularly publish articles covering new developments, tips, and best practices to help you make the most out of these tools. Don’t forget to follow Astrafy on LinkedIn to stay updated on the latest trends in data analysis 🚀.

Feel free to reach out to us at sales@astrafy.io if you seek for support with your Looker implementation, advice on Modern Data Stack or Google Cloud solutions.