Period-over-period (PoP) analysis – whether you’re looking at year-over-year, quarter-over-quarter, or month-over-month trends – is the bedrock of strategic decision-making. But in Looker doing PoP analysis has previously required the use of complex workarounds using LookML.
We’re excited to share that a new, streamlined approach to period-over-period analysis is here, and it’s designed to cut through the complexity and empower you with clear, actionable insights faster than ever before. The new period_over_period measure type in Looker is available now in Preview!
The new period_over_period measure type is flexible and easy to implement by defining a few straightforward parameters in LookML. Here’s what a new period_over_period measure looks like:
measure: orders_last_year_percent_change {
type: period_over_period
based_on: orders.count
based_on_time: orders.created_year
period: year
kind: relative_change
value_format_name: percent_2
}
The measure above would be used to analyze the percent change in number of orders year over year. There are four new parameters that need to be defined as part of a period_over_period measure:
based_on: This should reference an existing LookML measure. In the example above, we are looking at order counts year over year, so this PoP measure is based on the orders.count measure. The referenced measure should be one of the following types: AVG, SUM, MAX, MIN, COUNT or DISTINCT.
period: A Looker timeframe interval value representing the cadence of the measure. This could be Year, fiscal_year, quarter, fiscal_quarter, month, week, or date. For a year-over-year comparison, this parameter should be set to year.
based_on_time: A LookML field of type time that should be the basis of your PoP comparison. In our example, we are comparing orders year over year, so we use orders.created_year as the based_on_time. This field can use any of the various time frames from a time based dimension group.
kind: This parameter indicates the type of PoP analysis that you want to do and should be one of the following values:
previous: The value from the previous period.
difference: Subtract the previous period from the current period.
relative_change: (current period - previous period)/ previous period
The new period-over-period measure type will adapt to the timeframes that are selected in the Explore query. This means that you can compare values for the entire year over another year, but also compare equivalent months across years. For the example above, if we take that same year-over-year relative_change PoP measure and imagine that someone is using it in the Explore and the Explore query contains the orders.created_month timeframe dimension, the PoP measure will calculate relative_change of the monthly values and compare them across years (e.g. compare the relative_change of May 2023 vs May 2022).
In this gif I start off by selecting a PoP measure for year-over-year relative change for flight count. I then select the flight arrival yearly time dimension and add in a dimension for destination. My PoP measure dynamically adapts the calculation based on the dimensions selected in the Explore. Finally, I select a time dimension for arrival month to compare flight count by equivalent months across years. The new period_over_period measure type offloads the complexity of period-over-period calculations to Looker, and I can freely explore my data and gather insights.
As of Looker 25.6, we support period_over_period measures for all customers who are using BigQuery, Snowflake and Redshift connections (more to come!) and the new LookML runtime. More details on how to define and use period_over_period measures can be found in our documentation.
The new LookML period_over_period measure puts powerful insights at your fingertips and enables you to spend less time wrestling with your data and more time driving meaningful growth and achieving your business objectives.
Give it a try and let us know what you think by reaching out at looker_pop_feedback@google.com.
Cheers!