See the Methods for Period Over Period (PoP) Analysis in Looker Help Center article for more examples of period-over-period analyses in Looker.

The Basics

Period-over-period 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. The goal is to adjust the slice of the past you are looking at, so the same amount of time has passed in the two periods you are comparing.

How-To: Year-over-Year (YOY)

You can do period-over-period analysis over any defined time period. Our example is going to use year-over-year, because this is a very common use case. This example can be adjusted for comparing other time frames, such as month-over-month.

In year-over-year analysis, we're looking to compare the current year to date with last year up to the same date. But if we simply filter a query for the last two years, we are going to get all 365 days of the previous year.

In the example below, it looks like there have been significantly fewer orders placed compared to last year. This is not an accurate comparison, because this screenshot is taken in February 2019, when the current year is only partway over, and we're comparing this time frame to the entire prior year of 2018.

We can use custom filters to make sure that 2018 records are filtered to include only the records that occurred before this time last year. Here's an example of a custom filter we can use to accomplish this:

extract_months(${orders.created_date}) < extract_months(now()) OR
extract_months(${orders.created_date}) = extract_months(now()) AND
extract_days(${orders.created_date}) <= extract_days(now())

The Details

We can break this down a bit:

extract_months(${orders.created_date}) < extract_months(now())

The first condition looks to see if the month number is smaller than the current month number. If this is true, then that record is okay to include in our count. We use the extract_months function to pull out the number of the month, and the now() function to get the current date.

extract_months(${orders.created_date}) = extract_months(now()) AND
extract_days(${orders.created_date}) <= extract_days(now())

The second condition is for records that happened in the same month as the current month. When this happens, we'll want to look at the day of the month that the order took place to decide if we want to count it. If it is the same day or earlier, then it is okay to include that record in our count. We use the extract_days function to pull out the number of the day.

Looker has many functions and operators you can use in the custom filters box ( You can find more information on these in our Documentation).

When I use this custom filter, my counts become a lot more reasonable:

It's now apparent from this visualization that we have received more orders so far this year than we did in the same period last year.