Period Over Period Analysis in Omni Analytics

Have you ever tried to do a period-over-period analysis in Looker or Tableau? If not, you’d better buckle up for nested if statements, LAG functions, custom filters, and endless tinkering with table calculations to answer one simple question: “How did we perform vs. same time last year?”

Imagine doing all that with just a few clicks, little or no code, and no hacks in your BI Tool. Luckily, tools like Omni Analytics make this possible, and that’s what this blog is all about.

Period-over-period (PoP) analysis is at the heart of many data-driven decisions. It shows not only how you’re performing today, but how that performance stacks up against past benchmarks. At Data Culture, this analysis is often requested by our CPG clients to better understand their growth in e-commerce, retail, and wholesale channels. Whether you’re looking at month-over-month revenue, week-over-week traffic, or year-over-year customer growth, PoP gives context that raw numbers alone can’t.

In this guide, we’ll walk through Omni’s out-of-the-box Period-Over-Period controls you can leverage immediately. I will also cover more hands-on approaches to compare arbitrary periods. By the end of this article, the goal is to show you how to do this with just a few single clicks.

‍

1. Using Built-in PoP Controls

Omni’s native Period-over-Period controls let you swap between predefined offsets (e.g. previous period, year-ago, etc.) right from the dashboard UI.

How does this work?

• Select your date dimension and then right-click the three dots on the date field. Here you will find the period-over-period option out of the box.

• Once you select the Period over Period option, you will have the option to update the Current Period and Compare to buttons

• The next step here is to pivot the view by swapping rows and columns so that you can easily visualize this using the scorecard visualizer.

• And then once this is done, Omni calculates the % changes for you under the hood.

• Here is what the outcome will look like.

2. Comparing Periods over Arbitrary Periods

Omni does well in allowing you to compare specific time periods like week over week and year over year, but what if you wanted to do comparisons over arbitrary periods?"Arbitrary" here means any custom date ranges you choose - that could be different lengths of time, non-consecutive, or based on business events rather than calendar periods.For these previously mentioned CPG companies, a big focus is to compare growth across specific events or seasons. This could involve comparing Black Friday Sales with Summer Sales.

To enable these kinds of comparisons, we need to leverage filter-only fields, templated filters, and filtered measures.

Start by adding two filter-only fields in your view (they appear as date pickers in the UI). Under the filters: section of your view’s YAML, define current_period and previous_period with a type of timestamp (or date if you only need dates).

This creates two empty filters. In the workbook or dashboard, these will show up as date‐range selectors.
‍

filters:
  current_period:
    type: timestamp
  previous_period:
    type: timestamp

Next, define two boolean dimensions (flags) that test if each row falls in the selected period. Use Omni’s templated filter syntax to inject the chosen dates into your SQL.

previous_period_flag:
   sql: |
{{# reporting__marketing_report.previous_period.filter }} ${reporting__marketing_report.date} {{/ reporting__marketing_report.previous_period.filter }}

current_period_flag:
   sql: |
{{# reporting__marketing_report.current_period.filter }} ${reporting__all_digital_report.date} {{/ reporting__marketing_report.current_period.filter }}

This flag says: “Is this row’s date in the Previous Period that was selected?”, and the same is true for the current period flag. Rows in the chosen range will yield true, while the others will be false.

‍

Lastly, we define filtered measures for each period.

measures:
   count_previous:
     aggregate_type: count
     filters:
       previous_period_flag:
         is: true
   count_current:
     aggregate_type: count
     filters:
       current_period_flag:
         is: true

Count_previous counts records where previous_period_flag = true, i.e. rows in the user’s chosen “previous” date range. Likewise, count_current filters on the current_period_flag. You can apply this filtered measure on other measures of interest in your view. Then voila, you are able to compare arbitrary periods and the outcome is shown below.

‍

Summary

By following these steps, you can create clear period-over-period analyses in Omni. The user interface handles most of the work. Simply enable PoP, pick your date ranges, and view the side-by-side results. Using charts or pivot tables, you can easily see trends and growth rates. If you need more flexibility (like unusual date comparisons), Omni’s filter features let you build custom solutions. Now, your company can have a fuller story by overlaying current year, last year, and target metrics.

‍

Happy Analyzing!

‍