Using Retail Calendar for Period-over-Period Comparisons in Looker

Introduction

In this post, we will explore the implementation of Retail Calendars in Looker and delve into the advantages they offer. We will focus on how to leverage these calendars to build scalable Period Over Period (PoP) comparison functionalities. This includes importing retail calendar data into a Looker view, extending the view, and creating new dimensions to enable seamless period-based analysis. We will also cover how to join these calendars to explores, ensuring flexibility in reporting. Additionally, we will walk through implementation details with code snippets and provide comparison examples to illustrate the impact of these techniques.

By the end of this blog, you will have a robust, user-friendly solution to perform powerful period comparisons, such as analyzing a Retail Month against the same month in the previous year.

What is a Retail Calendar?

A Retail Calendar is a specialized calendar used in the retail industry to track sales and inventory over specific periods, often aligning with seasonal trends and business cycles. Unlike a standard calendar, retail calendars such as the 4-5-4 calendar are structured to maintain consistent weeks for accurate year-over-year (YoY) comparisons. For a detailed overview, the NRF website provides an excellent explanation.

---

​Implementation: Standard Calendar vs. Retail Calendar

Standard Calendar in Looker

Looker uses a standard calendar by default, based on the Gregorian system. You can customize the starting day of the week (e.g., Sunday or Monday) by including week_start_day: sunday in the model configuration. While this works well for general reporting, it falls short when comparing periods with varying numbers of days or inconsistent week structures.

​Implementing a Retail Calendar in Looker

To implement a retail calendar:

• Create a View: Start by creating a view containing retail calendar data. This data can be sourced from:
  • A Google Sheet with updated dates.
  • SQL queries to generate the necessary calendar structure.
• Note that different businesses may adopt other variations, such as a 4-4-5 calendar. One such example can be found here : Google Sheet Link
• Join the View: Join the retail calendar view on the date field with the date field of any other view where needed. This ensures the retail calendar’s alignment with other datasets in your model.

---

Why Use a Retail Calendar in Looker?

The primary advantage of using a retail calendar lies in its ability to compare identical periods containing the same number of days, weekends, and holidays. For instance, comparing January of this year with January of last year using the Gregorian calendar can lead to discrepancies due to varying week structures. A retail calendar resolves this issue, ensuring accurate YoY comparisons.
If you haven’t already, we recommend checking out our foundational approach to building Period Over Period capabilities in Looker, as this blog builds upon that methodology. Our approach overcomes some of the limitations of Looker’s default setup, especially in terms of scalability.

---

Example: Integrating Retail Calendars into PoP Analysis

For a recent project, we implemented a 4-4-5 retail calendar to meet client-specific needs. Here’s how:

Import Retail Calendar Data:

• Use a Google Sheet (or other sources) containing 4-5-4 calendar dates.
• Sample sheets for 4-5-4 or 4-4-5 retail calendars can be easily found online.

Extend the View:

• Create a Period Over Period (PoP) view by extending the retail calendar view.
• This allows predefined analysis periods and breakdowns, which can be scaled across multiple explores, avoiding code replication.

Create new Dimensions in the Extended view

• ​​PoP Pivot Dimension​
  ​​A pivot dimension consisting of the time periods selected for comparison.​
  

• PoP Row Dimension​
  ​​Date breakdown to be shown in rows for respective comparison.​
  

• ​​Sort By
  Sort by field ensures that the PoP Pivot and PoP Rows are displayed in logical order.
  

• Filter Date
  ​​The Filter Date will always have to be filtered with the Analysis Period to ensure dates are filtered according to the Analysis Period selected.

• To-Date
  

Join to Explores:

​Join the PoP and retail calendar views to any required explore. For example, if the explore is named daily_sales, the join relationship will depend on the nature of the daily_sales table.

Add Comparisons:

Use these joins to add retail period comparisons to PoP analysis. Examples include:

• This Retail Month vs. Same Retail Month Last Year.
• This Retail Quarter vs. Same Retail Quarter Last Year.

The below screenshots show how all the dimensions work with and without to date.

The above illustration shows the comparison of This Retail Month vs Same Retail Month Last Year where we use To-Date comparisons

The above illustration shows the comparison of This Retail Month vs Same Retail Month Last Year where we do not use To-Date comparisons

---

Conclusion

By integrating a retail calendar into Looker, you can significantly enhance your ability to perform accurate period comparisons. This approach not only ensures consistency but also scales seamlessly across multiple datasets and explores. Whether you’re working with a 4-5-4 or 4-4-5 calendar, this solution empowers you to make data-driven decisions with precision and ease.
Ready to implement this in your Looker instance? Dive in and see how simple it can be to create powerful, scalable period-over-period analyses!​

---

Ashish Masand

Lead Data Analyst

Shridhar Patil

Data Analyst