Period-over-Period in Looker: A Quick Guide

10/21/2024

Introduction
In this article, we’ll walk through how to implement Period Over Period (PoP) comparisons in Looker. While Looker suggests a few approaches for comparing different time periods, we will introduce a customized approach developed by our team at Nupanch. Our approach highlights enhancements that can be made to Looker’s suggested methods. This guide includes code snippets and example outputs, so you can easily replicate these techniques in your own Looker instance.

Understanding Period Over Period Comparisons
Before diving into the implementation, let’s quickly explore why Period Over Period comparisons are so useful. When measuring performance, it’s critical to compare current performance against previous periods. Some common comparisons include:

This year vs. last year
This month vs. last month
This quarter vs. same quarter last year

PoP comparisons help you identify trends and patterns, enabling better business decision-making. For instance, you might want to track:

How your business performed this year compared to last year
Whether you hit your sales targets for this quarter
Business performance for specific periods like Cyber Weekend

Looker’s Built-in Methods for PoP Analysis
Looker provides a few built-in methods for performing PoP comparisons:

Any Two Native Timeframes: Compare two predefined periods, broken down by days, weeks, or months.
Current and Previous Periods: Filter for the current period and compare it to the previous year, month, or week.
Arbitrary Periods: Select any two periods for comparison, typically broken down by days, allowing you to see totals for each period.

For more details on these methods, check out Looker’s support article.

Limitations of Looker’s Built-in Methods
While Looker’s native methods work well, there are some limitations:

Hard to scale. The logic can only be used within a single view
Predefined periods may not suit all use cases.
Filtering options are limited, and may not allow for custom periods.
Arbitrary Period comparisons are often restricted to days, limiting flexibility.

Because of these limitations, we at Nupanch developed a customized approach tailored to the needs of our clients.

Nupanch’s Approach to PoP Analysis in Looker
Our approach simplifies Period Over Period analysis by creating predefined "analysis periods" and "breakdowns." This method uses one parameter, one filter, and several dimensions that help filter dates according to the selected parameter. Here’s how it works:

This Week vs Same Week Last Year (WoWLY): Compares the current week with the same week last year, with a filter to adjust for incomplete weeks.
This Month vs Same Month Last Year (MoMLY): Compares the current month with the same month last year, adjusting for incomplete months.
This Year vs Last Year (YoYLY): Compares the current year to the previous year, with adjustments for incomplete years.
Cyber 5 This Year vs Cyber 5 Last Year: Compares Cyber 5 periods from two consecutive years. For example, to compare 2023 vs. 2022, we would look at the respective date ranges (Nov 23-28, 2023 vs Nov 24-29, 2022). The filter subtracts 364 days to create accurate comparisons.

Furthermore, by using a date dimension table we are able to scale the period-over-period capability to several explores.

Implementing a view to enable period over period dimensions in Looker

To implement this:

Create view based on a date dimension table.
Create an extended View: Start by creating a view which extends the date dimension view created above to add the custom logic that enables PoP (this custom logic is mentioned below)
Join the View: Join the extended date dimension period over period view on the date field with the date field of the other view where needed.

Our Customized PoP Analysis Solution

1. PoP Pivot Dimension
The PoP Pivot dimension compares two periods directly, visualizing them in charts like lines or bars. This allows us to see each period (e.g., "This Month" vs "Same Month Last Year") in separate visual elements. For example, the values might look like:

04-2024 (current period) vs 04-2023 (last year)

Note : The code snippets below are built using Snowflake syntax. Please modify the syntax to match code base.

The values “MTDoMTDLY” etc. indicate the specific comparison being made. For example, “MTDoMTDLY” is used for Month to Date vs. Month to Date LY.

2. PoP Row Dimension
The PoP Row dimension breaks down periods into granularity like days, weeks, or months. Since the dates will differ between comparison periods, we use metrics like "Week of Year" or "Day of Year" rather than specific dates.

3. Sort By
Sorting ensures that the PoP Pivot and PoP Rows are displayed in logical order. For instance, in a Cyber 5 comparison, we want to see the rows ordered as Thursday, Friday, Saturday, Sunday, and Monday.
We accomplish this by sorting based on a combination of "week of year" and "day of week."

Sort_by1 sorts the PoP Row dimension.
Sort_by2 sorts the PoP Pivot dimension if necessary.

4. Filter Date
The Filter Date helps to accurately filter date ranges for the periods you’re comparing. For example, if you’re comparing "This Month vs Same Month Last Year," Filter Date selects the appropriate date ranges for analysis by setting the start and end dates for both periods.

5. To-Date
The To-Date filter ensures you're comparing “apples to apples” by matching incomplete periods. For instance, if today is Wednesday and you’re comparing this week to the same week last year, To-Date ensures that only the first three days of data are compared, making the analysis fair.

Without the To-Date Filter

With the To-Date filter

As seen above with the To-Date filter you can limit the previous period to the days in the current period and compare apples to apples.

Conclusion
Implementing Period Over Period analysis in Looker can be straightforward with built-in methods, but it becomes even more powerful when customized to meet specific business needs. Our approach allows you to create predefined periods, compare them more effectively, and visualize the data in meaningful ways.
If you’d like assistance implementing this in your Looker environment, don’t hesitate to reach out to us at Nupanch.

Appendix: Key Code Snippets
Here are some handy snippets for reference when setting up your analysis:
Current_date :