Skip to main content

Period-over-period Comparison

Alternative

If you want more flexibility, check out our Period Comparison Guide using AQL.

Introduction

With Period Comparison feature, users can quickly visualize and compare data of different periods without complicated modeling steps.

For example, comparing sales of this year with the previous year:

Set up

Period Comparison can be set up both when you work with dashboards and dataset exploration, though the steps can be a bit different. We will go through each scenarios in the following sections.

When working dashboards

  1. On Dashboard: Click on the Add Control button, and choose Period Comparison.
  2. On the “Add Period Comparison” modal:
    • Enter a label for your comparison
    • Select the Comparison type & Period to compare
    • Select the widgets that you want to apply the comparison, and choose a date/time field to act as the time dimension of the comparison.
Notes
  • Each widget can be mapped to only one Dashboard Period Comparison at a time
  • If a widget already has Period Comparison in its settings, Dashboard Period Comparison will take precedence when you view it in dashboard mode. The widget's Period Comparison remains in tact, and will show when you Explore or Edit the widget.

When exploring datasets

  1. Select at least one measure/metric from dataset, or create an aggregation
  2. Toggle on Period Comparison in Visualization Settings
  3. Select a date/time field to act as the time dimension of the comparison
  4. Select Comparison Type & Period to compare

For non-time series charts

Holistics's Period Comparison also works on non-time series charts.

However, since they display aggregated data without a time dimension, the setup will need a few extra steps.

  • If you use Previous period comparison, you will need to specify a base time period so that Holistics can take that period and shift it back. In other words, you need to add a time filter to your data. Without a filtering condition, two periods will return the same result.

  • If you use Custom period comparison, without filtering on the base time period, the feature still works. You will compare metrics calculated on all time against metrics calculated on a custom time period.

How it works

Period Comparison supports two types of comparison:

  • Previous period: a relative time shift from the original timeframe. For example: compare this month with 2 months ago
  • Custom period: an absolute timeframe chosen by the user. For example: compare this week with the Christmas period (2023-12-24 to 2024-01-02)

We will go into details of how each type works in the next sections.

Previous period comparison

Previous period comparison will take the base time period, shift it back by an amount of your choice (1 month, 1 year...), calculate your metrics at that shifted time period, and display the time points of base period and past period in parallel. For example:

  • Previous 1 year comparison: June 2023 is compared to June 2022
  • Previous 1 week comparison: This Friday is compared to last week's Friday

Custom period comparison

Custom period comparison will calculate the metrics at the custom selected time period, and the two time periods are displayed together so that the first time points are aligned. Time points in both periods are sorted in the same order.

In the following example, the custom period (09/2022 -> 12/2022) is compared with the base period (01/2023 -> 06/2023). Data points are sorted in descending time order, and the month 12/2022 is aligned with the month 06/2023 as they are both at the first position of their respective period.

Availability

Chart types supported

The following chart types support Period Comparison:

  • Table
  • Pivot Table
  • Line/Bar Chart family: Line/Bar/Column/Area/Combination chart
  • Metric KPI

Databases supported

Period Comparison is supported in the following databases:

  • PostgreSQL
  • Google BigQuery
  • Amazon Athena
  • Snowflake
  • Presto
  • Amazon Redshift
  • MySQL
  • Microsoft SQL Server
  • Clickhouse

Other notes

Date filters only apply to the base period

If users want to limit the data from both periods for privacy purposes, we suggest using Row-level Permission.

When base and past periods have different length

For example, we compare the base month with previous 1 month.

  • If the base month has more days than the previous month (March vs. February), then the line for previous month will show blank for the missing days:

  • If the base month has less days than the previous month (February vs. January), then the chart will cut off at the end of the base month:

Comparing weeks of years

If the data is aggregated to Week grain and is to be compared with previous 1 year, we will do the following:

  1. We convert the weeks into seven-day blocks. The first day of the week follows Week Start Day setting.
  2. We shift those seven-day blocks back one year to create the past period
  3. The metrics calculated for this past period will be aggregated by these seven-day blocks and may not represent a normal week (that starts from Monday and ends with Sunday)

In other words, Week 1 of this year may not be aligned with the days of Week 1 last year, but is aligned with the seven-day block that happened exactly 1 year ago.

For example, when comparing weekly Revenue of January 2021 and February 2021 with their previous 1 year. The Week Start Day is Monday.

On the Roadmap

Below is the list of features that are to be implemented in the future:

  • Select preceding period
  • Compare more than two periods
  • Select Delta, Growth Rate, etc, between two values

The order above is not our prioritized order. Feel free to reach out to us at [email protected] if you have any suggestions for improvement.


Let us know what you think about this document :)