Period-over-period (PoP) analysis is a pattern of analysis that measures something in the present and compares it to the same measurement in a comparable period of time in the past.
For dialects that support period-over-period measures, Looker developers can add PoP measures to LookML projects to enable PoP analysis in the corresponding Looker Explores.
For example, the following Looker Explore query shows the number of orders that were created in the current month, along with PoP measures for the number of orders created last year, the difference from last year, and the percentage change from last year. You can verify the year-over-year comparison by spot-checking the values. For example, the value for Orders Last Year for 2012-03
is the same as the value for Orders Count for 2011-03
:
To add a PoP measure to a LookML project, a Looker developer must create a measure
of type: period_over_period
and include the subparameters that are described in the following section of this page.
For example, here is the LookML for a PoP measure that provides the order count for the previous year:
measure: order_count_last_year {
type: period_over_period
description: "Order count from the previous year"
based_on: orders.count
based_on_time: orders.created_year
period: year
kind: previous
}
This PoP measure has the following attributes:
- It is defined with
based_on: orders.count
, so the PoP measure will provide data about order count from the previous time period. - It is defined as
kind: previous
, meaning that it provides the count value from the previous time period (opposed to providing a difference in order count from the previous time period, or a percentage of change in order count from the previous time period). - It is defined with
period: year
, so it will provide order counts from a comparable amount of time from the previous year.
Subparameters of PoP measures
A PoP measure is a measure
of type: period_over_period
that includes the subparameters that are described in the following sections:
As described in the Explore queries with PoP measures section, PoP measures calculate their values based on both the PoP measure's LookML definition and the fields in an Explore query. Because of this, you should adhere to the following best practices when creating a PoP measure in LookML:
- Provide to your Explore users an indication of the PoP measure's
period
, either in the name of the PoP measure or in the measure'sdescription
subparameter. - Provide to your Explore users an indication of the PoP measure's
based_on
measure, either in the name of the PoP measure or in the measure'sdescription
subparameter.
For example, the following PoP measure is named order_count_last_year
, and a description is included to let users know that the measure provides the number of orders from the previous year:
measure: order_count_last_year {
type: period_over_period
description: "Order count from the previous year"
based_on: orders.count
based_on_time: orders.created_year
period: year
kind: previous
}
based_on
Use the based_on
field to specify the LookML measure that the PoP measure is based on. For example, if you base a PoP measure on the orders.count
field, the PoP measure can provide the number of orders from a previous time period so that you can compare the number of sales between a current period and a previous period.
In the based on
field, you must specify one of the following types of measures:
based_on_time
Use the based_on_time
subparameter to provide Looker with a time field to use to calculate the PoP measure values. The based_on_time
subparameter must specify one of the following timeframes of a dimension group of type: time
:
year
fiscal_year
month
fiscal_quarter
quarter
week
date
raw
For the based_on_time
field, the specific timeframe that you use is irrelevant -- you just need to point the PoP measure at a dimension group of type: time
so that the PoP measure can use the underlying timestamp of the dimension group. You can't specify a timeframe from a dimension group of type: duration
; duration type dimension groups are not supported and will produce a runtime error in the Explore.
kind
Use the kind
parameter to specify the type of calculation that you want the PoP measure to make for the previous period. You can specify one of the following values for kind
:
previous
: (default) The value from the previous period.difference
: The difference between periods (the previous period subtracted from the current period).relative_change
: The percentage change from previous period. The percentage change is calculated by the following equation:$$ relativeChange = (current - previous)/previous $$
period
Use the period
subparameter to specify the PoP measure's cadence, how far back you want to jump in your comparison. For example, a PoP measure that's defined with period: year
will show the values for the previous year. If you run an Explore query on monthly order count, the period: year
PoP measure will show the values for the same month in the previous year, so that you can compare the order count for November 2025 to the sales count of November 2024.
The period
subparameter supports the following values:
year
fiscal_year
quarter
fiscal_quarter
month
week
date
Explore queries with PoP measures
The calculation that's performed for a PoP measure is based on the PoP measure's LookML definition and also on the timeframes that are specified in the Explore query itself; the PoP measure adapts its calculation to the timeframes that are selected in the Explore query. For example, if the PoP measure is defined with period: year
, and the Explore query contains the orders.created_month
timeframe dimension, the PoP measure will calculate monthly values, comparing January 2025 to January 2024. If you want to see the yearly values, you would have to run an Explore query with the PoP measure and only the orders.created_year
timeframe.
Here are some examples of how a PoP measure's period
interacts with the timeframes that are selected in an Explore query:
- If a PoP measure is defined with
period: year
, and you run an Explore query with a quarter timeframe, the PoP measure will return values from the same quarter in the previous year (Q1 of 2025 compared to Q1 of 2024). - If a PoP measure is defined with
period: year
, and you run an Explore query with a month timeframe, the PoP measure will return values from the same month in the previous year (April 2025 compared to April 2024). - If a PoP measure is defined with
period: month
, and you run an Explore query with a month timeframe, the PoP measure will return values for the previous month (April 2025 compared to March 2025).
Requirements for Explore queries with PoP measures
Because a PoP measure makes calculations based on both the LookML definition of the PoP measure and the fields that you select in the Explore query, at a minimum you must include the following fields in an Explore query that has a PoP measure:
- The PoP measure.
A timeframe from the same dimension group as the PoP measure's
based_on_time
parameter:- The timeframe in the Explore query must be an equal or smaller timeframe than what is specified in the
period
parameter of the PoP measure. For example, if the PoP measure'sbased_on_time
is defined with a timeframe from theorders.created
dimension group and the PoP measure is defined withperiod: month
, the Explore query must include a timeframe from theorders.created
dimension group that is equal to or smaller than a month, such asorders.created_date
. The timeframe in the Explore query must be smaller because, for example, you can't do a month-over-month comparison of a year timeframe. - The timeframe in the Explore query must itself contain timestamp information. For example, the
year
,month
, anddate
timeframes of a dimension group provide actual timestamp information. In contrast, theday_of_week
timeframe is abstracted from the underlying timestamp to provide a value such asWednesday
. Similarly, timeframes such asmonth_name
,month_num
, andday_of_month
don't provide timestamp information themselves, so they can't be used by PoP measures to calculate values for the previous period. However, if you include in the Explore query a timestamp such asdate
, that will provide the PoP measure with timestamp information that it can use to calculate values for the previous period. You can also include theday_of_week
timeframe in the Explore query, because the PoP measure can use thedate
timeframe information for calculations.
- The timeframe in the Explore query must be an equal or smaller timeframe than what is specified in the
As long as you meet these requirements in your Explore query, you can add other fields and timeframe dimensions in the Explore query, but all of the timeframes in the Explore query must be equal to or smaller than the timeframe from the PoP measure's period
timeframe. When you run an Explore query with a PoP measure, Looker automatically applies the minimum timeframe granularity from the query to the timeframe used by the PoP measure. In the example Explore shown at the beginning of this page, the PoP measures have all been defined in LookML with period: year
. This means that for whatever timeframe is selected in the Explore query -- in this case, a monthly timeframe -- the PoP measure will return the results for the same timeframe in the previous year.
If you want to see which timeframes are supported with your PoP measure in an Explore, you can test different timeframes without having to run queries. Click the SQL tab of the Explore's Data section, and then add fields and filters from the Explore's field picker. If the PoP measure can't calculate your the query with your selected fields and filters, the SQL tab will show a message that the SQL can't be generated.
If you do run a query where the SQL can't be generated, the Explore window will return an error with the details and a link to the relevant LookML.
Example
Here is the LookML for an example total_births
measure, a birth
dimension group of type:time
, and two PoP measures that are based on the total_births
measure and that use the birth
dimension group as their based_on_time
field:
dimension_group: birth {
type: time
timeframes: [raw, time, date, week, month, quarter, year]
sql: ${TABLE}.birth_date ;;
}
measure: total_births {
type: sum
sql: ${TABLE}.total_births ;;
}
measure: total_births_last_year {
type: period_over_period
kind: previous
based_on: total_births
based_on_time: birth_year
period: year
value_format_name: decimal_0
}
measure: total_births_last_month {
type: period_over_period
kind: previous
based_on: total_births
based_on_time: birth_year
period: month
value_format_name: decimal_0
}
Note the following about these fields:
- Both of the PoP measures are defined with
kind: previous
, so they both provide the value of the measure from the previous period. - Both of the PoP measures are defined with
based_on_time: birth_year
, so they both use the underlying timestamp of thebirth
dimension group. - The
total_births_last_year
PoP measure is defined withperiod: year
, and thetotal_births_last_month
PoP measure is defined withperiod: month
.
Here is an Explore query that includes all three of the measures and the birth_month
dimension timeframe:
Note the following about the Explore results:
- The smallest dimension timeframe in the Explore query is
birth_month
, so the PoP measure provides monthly values. - In the row for the most recent month, 2024-07, the Total Births Last Month value shows the total births for the previous month, 2024-06. You can verify this by looking at the Total Births value for the 2024-06 row. The two values match.
- In the row for the most recent month, 2024-07, the Total Births Last Year value shows the total births for the same month (07) in the previous year (2023). You can verify this by looking at the Total Births value for the 2023-07 row. The two values match.
Filtering Explore queries with PoP measures
Filtering is supported for Explore queries with PoP measures. However, when you filter on a field that's associated with the based_on_time
parameter of a PoP measure, if the timeframe of the filter is finer than the timeframe of the query, the PoP measure will show only the results for the filter-value portion of the query's timeframe.
For example, if you query on the orders.created_year
dimension and you filter the query for the month of January, for each year the PoP measure will show the values for January only. This can be mistaken for being the results for the full year.
The Public Preview of PoP measures has the following limitations for filtering queries with PoP measures:
- Filters on
month_num
ormonth_name
dimension group timeframes are not supported for Explores that query on aquarter
dimension timeframe. - Filters on
fiscal_month_num
ormonth_name
dimension group timeframes aren't supported for Explores that query on afiscal_quarter
dimension timeframe.
Visualizations with PoP measures
The table chart visualization is recommended for PoP measures. Other visualization options may work as well, depending on the fields in your Explore query.
If you use a visualization other than a table chart, verify that your visualization is clear. Because PoP measures provide comparisons to a previous time period, visualizations with PoP measures may be misleading. For example, a year-over-year PoP measure that's defined as kind: previous
will show last year's value for this year's date. If your Explore query includes the current year's value along with the year-over-year PoP measure, the current year will have two values in the visualization.
If you use a visualization other than a table chart, verify that your visualization clearly indicates that any PoP measures are a comparison to a previous time period.
Limitations for PoP measures
Note the following limitations of PoP measures:
- PoP measures must be based on an aggregate measure, as described in the
based_on
section. You cannot base a PoP measure on a non-aggregate measure. - For BigQuery connections, PoP measures are supported only if the BI Engine Symmetric Aggregates Labs feature is disabled.
- PoP measures don't support cohort analysis.
- PoP measures don't support rolling calculations.
- PoP measures always compare the current period to the previous period. You can't configure a PoP measure to compare the current period to a different period other than the previous period. For example, you cannot create a PoP measure to compare May of last year to December of this year.
- PoP measures are not supported with custom calendars, such as retail 4-5-4 calendars. See the
period
section for the time periods that PoP measures support. - PoP measures are not supported with custom periods, such as the current two weeks compared to the previous two weeks.
Liquid parameters are not supported in the parameters of a PoP measure. However, if the
based_on
orbased_on_time
fields of a PoP measure point to a dimension that is defined with Liquid, that Liquid will be processed.PoP measures are not supported with the following Looker features:
PoP measures can't be used to create a custom field.
You can't select the week timeframe in an Explore query with a PoP measures unless the PoP measure is defined with
period: week
orperiod: date
.PoP measures with periods that are defined with fiscal timeframes can't be used in Explore queries with non-fiscal timeframes. Also, PoP measures with periods that are defined with non-fiscal timeframes can't be used in queries with fiscal timeframe dimensions.
PoP measures support fiscal month offset, in that the PoP measure's
based_on_time
parameter will inherit thefiscal_month_offset
value from the LookML model file that is associated with the Explore. If you define a PoP measure withfiscal_year
orfiscal_quarter
, the PoP measure will be supported in an Explore query only if the Explore query specifies afiscal_year
orfiscal_quarter
timeframe. In that case, thefiscal_offset_month
is honored.The
period
of the PoP measure must be equal to or larger than the timeframe that's selected in the Explore query. For example, for a PoP measure that's defined withperiod: month
, the Explore query must have a timeframe dimension of a month or smaller, such as week or day.
Supported database dialects for PoP measures
The following table shows which dialects support PoP measures in the latest release of Looker:
Dialect | Supported? |
---|---|
Actian Avalanche | No |
Amazon Athena | No |
Amazon Aurora MySQL | No |
Amazon Redshift | Yes |
Apache Druid | No |
Apache Druid 0.13+ | No |
Apache Druid 0.18+ | No |
Apache Hive 2.3+ | No |
Apache Hive 3.1.2+ | No |
Apache Spark 3+ | No |
ClickHouse | No |
Cloudera Impala 3.1+ | No |
Cloudera Impala 3.1+ with Native Driver | No |
Cloudera Impala with Native Driver | No |
DataVirtuality | No |
Databricks | No |
Denodo 7 | No |
Denodo 8 | No |
Dremio | No |
Dremio 11+ | No |
Exasol | No |
Firebolt | No |
Google BigQuery Legacy SQL | No |
Google BigQuery Standard SQL | Yes |
Google Cloud PostgreSQL | No |
Google Cloud SQL | No |
Google Spanner | No |
Greenplum | No |
HyperSQL | No |
IBM Netezza | No |
MariaDB | No |
Microsoft Azure PostgreSQL | No |
Microsoft Azure SQL Database | No |
Microsoft Azure Synapse Analytics | No |
Microsoft SQL Server 2008+ | No |
Microsoft SQL Server 2012+ | No |
Microsoft SQL Server 2016 | No |
Microsoft SQL Server 2017+ | No |
MongoBI | No |
MySQL | No |
MySQL 8.0.12+ | No |
Oracle | No |
Oracle ADWC | No |
PostgreSQL 9.5+ | No |
PostgreSQL pre-9.5 | No |
PrestoDB | No |
PrestoSQL | No |
SAP HANA | No |
SAP HANA 2+ | No |
SingleStore | No |
SingleStore 7+ | No |
Snowflake | Yes |
Teradata | No |
Trino | No |
Vector | No |
Vertica | No |