Looker Period-over-Period analysis: A flexible approach

[corgi-looker]

Period over Period (PoP) analysis is a cornerstone of understanding business trends, identifying growth areas, and spotting potential issues. Whether you’re comparing this month’s sales to last month’s, or this year’s performance against the previous year, the ability to easily visualize these comparisons is crucial.

While Looker offers various methods for achieving PoP analysis (as helpfully outlined in the Google Cloud Community article), many approaches involve relative date calculations or require pre-aggregated data in persistent derived tables. Today, we’re excited to share a more flexible method we call “Flexible PoP.”

This blog will be broken up into 3 sections. The first will outline how to set up the LookML, the second the visualization, and the third will dive deeper into specifics of both the LookML and Custom Config Editor.

This approach leverages the power of LookML cross joins and some clever visualization techniques to provide a direct, date-centric comparison without the need for complex PDTs or relative X-axis values. Our “Flexible PoP” solution has two key components: the foundational LookML and the intuitive visualization configuration.

A flexible approach to Period over Period:

This LookML approach offers several advantages:

• Date-centric X-axis: Unlike some PoP methods that shift the X-axis to relative values (like “Day of Month”), our approach keeps the X-axis anchored in actual dates, making it more intuitive to interpret trends over time.
• Simplicity: The logic is straightforward and easy to understand.
• Flexible time periods: Users can analyze data starting from any date and for any duration, comparing it to the immediate prior period (day, week, month, or year).
• Flexible measures: Users can select any measure they want from the original table adding a level of flexibility that is traditionally very difficult to do with other POP methods.
• No PDTs required: By leveraging cross joins and dynamic date calculations, we avoid the need for persistent derived tables or materialized views, simplifying your LookML and potentially improving query performance.

Part 1: The magic of LookML - “Flexible PoP”

At the heart of our solution lies a simple yet powerful LookML structure that creates a “copy” of your data for easy comparison. Let’s dive into the code:

#downsides: Must pivot on periods ago, else risk incorrectly showing multiple periods together without any warning to user
connection: "[YOUR_CONNECTION_NAME]"

include: "//order_items.view.lkml"

explore: order_items {
  join: pop_support {
    type: cross
    relationship: one_to_one
  }
  #consider a sql_always_where to exclude 'future' data that manifests as a result of POP logic
}

# we will fan out the data to get extra copies, and we'll offset dates for POP.
view: pop_support {
  derived_table: {
    #could do fancier logic here to allow additional periods, for example
    sql:
    select 0 as periods_ago union all
    select 1 as periods_ago
    ;;
  }
#MUST PIVOT ON THIS FIELD!!!!
  dimension: periods_ago {
    type:  number
    description:  "Must Pivot on this Field"
  }

dimension_group: pop_date {
    type: time
    timeframes: [date,month,year]
    sql: timestamp(date_add(date(${order_items.created_at_raw}), interval ${periods_ago} {% parameter period_offset %}));;
  }
# Required Field
  parameter: period_offset {
    type: unquoted
    allowed_value: {value:"year"    label:"year"}
    allowed_value: {value:"week"    label:"week"}
    allowed_value: {value:"month"   label:"month"}
    allowed_value: {value:"quarter" label:"quarter"}
  }

  measure: max_created {
    type: date
    sql: max(timestamp_trunc(${order_items.created_at_raw}, {% if pop_support.pop_date_month._is_selected %}MONTH{% elsif pop_support.pop_date_year._is_selected %}YEAR{% else %}DAY{% endif %}));;
  }
}

How it works:

1. Cross join approach overview: We use a cross join to a new view called pop_support. This creates every combination of rows from your base order_items Explore with the rows from pop_support.
2. Pop_support view:

   • derived_table: This simple derived table generates two rows: one with periods_ago = 0 (representing the current period) and another with periods_ago = 1 (representing the previous period). You could extend this for more historical comparisons if needed.

   • dimension: periods_ago: This is the key! You must pivot on this dimension in your Explore to separate the current and previous period data into distinct columns. Failing to do so will result in combined data without a clear distinction.

   • dimension_group: pop_date: This dimension group dynamically adjusts the date based on the periods_ago value and the selected period_offset parameter. For the current period (periods_ago = 0), no date adjustment occurs. For the previous period (periods_ago = 1), the date_add function offsets the ‘Prior’ copy of the data (that was added by the pop_support join) one unit of the chosen period_offset

   • parameter: period_offset: This parameter allows users in the Explore to easily switch between different comparison periods or use unique comparisons like comparing to 4 weeks prior instead of comparing to one month prior.
     

     

   • measure: max_created: This measure calculates the maximum date within the selected time period for both the current and previous periods. This will be crucial for creating our secondary X-axis in the visualization.

End result:
You should now be able to create a report that looks similar to the one below. 0 represents the current period and 1 the period prior. The Period is defined by the Parameter “Pop Support Period Offset” in the below case it’s year and the range can be defined as a filter on “Pop Support Pop Date Month”. Take a moment to play around by changing the pop date granularity, offset’s, date filters, and measures to see it in action.

While this is helpful, the line chart doesn’t do a great job of highlighting which dates the previous periods are and what the values of those dates are. The next part of this blog will seek to remedy this.

Part 2: Crafting the visualization - a dual-axis line chart

Now that we have our LookML foundation, let’s build the visualization that brings our PoP analysis to life. We’ll be using a Line Chart and leveraging Looker’s powerful custom chart configuration.

1. Initial Explore setup:

To create the visualization, ensure your Explore is set up with the following fields in this specific order:

1. pop_support.pop_date_month (Select the desired time period - Date, Month, or Year - from the Pop Date dimension group).
2. [Your View Name].[Your Measure] (Select the measure you want to analyze, e.g., order_items.order_count). This method is flexible and works with any measure from your base Explore.
3. pop_support.max_created
4. pop_support.periods_ago (Pivot on this field!)
5. pop_support.period_offset parameter

2. Pivoting is essential:

Remember to pivot your query on the pop_support.periods_ago dimension. This will create two columns for your measure and the max_created date: one for periods_ago = 0 (current) and one for periods_ago = 1 (previous).

3. Initial chart configuration:

1. Select the Line chart visualization.
2. Navigate to the Edit menu of your visualization.
3. Under the Y tab, drag the pop_support.max_created measure to the Right Axis. We’ll be customizing this axis later.
4. Adjust the Series Labels for the measures to say “Current” for the 0 count measure and “Previous” for the 1 - count measure.
   
   

4. Custom chart configuration:

Now for the final touch – the custom chart configuration. Click on the Edit and select the Plot Menu, at the bottom you will find Edit Chart Config. Paste the following JSON code into the editor:

{
series: [{
name: 'Current',
color: "orange",
lineWidth: 2
},
{
color: "transparent",
states: {
hover: {
enabled: false
}
},
showInLegend: false
},
{
name: 'Previous',
color: "grey",
lineWidth: 2,
dashStyle: "Dash",
},
{
color: "transparent",
states: {
hover: {
enabled: false
}
},
showInLegend: false
},
],
tooltip: {
shared: true,
format: "<span><b><span style=\"color:{points.2.color};\">Previous</span></b><br>{points.3.y:%Y-%m-%d}: {points.2.y:.1f}<br><br><b><span style=\"color:{points.0.color};\">Current</span></b><br>{points.0.x:%Y-%m-%d}: {points.0.y:.1f}</span>"
},
xAxis: [{
tickLength: 0,
labels: {
format: "<b><span style=\"color: orange;\">{value: %Y-%m-%d}</span></b>"
},
type: "datetime"
},
{
opposite: true,
linkedTo: 0,
tickLength: 0,
type: "datetime",
labels: {
format: "<b><span style=\"color: grey;\">{(subtract value (subtract chart.userOptions.series.3.data.0.x chart.userOptions.series.3.data.0.y)):%Y-%m-%d}</span></b>"
}
}
],
yAxis: [{ }, 
{
visible: false 
}] //hide secondary axis
}

Decoding the configuration:

Let’s break down what this custom configuration does:

• series: This array styles our lines:

• The first object (index 0) styles the current period’s measure line in orange with a solid line.

• The second object (index 1) makes the legend entry for the current period’s max date transparent and disables hover effects, effectively hiding it from the user.

• The third object (index 2) styles the previous period’s measure line in grey with a dashed line.

• The fourth object (index 3) does the same as index 1, hiding the legend and hover for the previous period’s max date.

• tooltip: This customizes the tooltip that appears when you hover over the data points:

• shared: true combines the information for all series at the hovered point.

• The format string creates a clear and informative tooltip, labeling the “Previous” and “Current” periods with their respective colors, dates (from the max_created fields), and measure values. It cleverly accesses the correct data points based on their order in the underlying data.
  

  

• X-axis: This configures our X-axes:

• Primary X-axis (index 0): This displays the dates for the current period in orange. tickLength: 0 removes the axis ticks for a cleaner look.

• Secondary X-axis (index 1):

• opposite: true places this axis at the top of the chart.

• linkedTo: 0 synchronizes it with the primary X-axis.

• The magic happens in the labels.format. This JavaScript expression dynamically calculates the corresponding dates for the previous period and displays them in grey. It subtracts the difference between the starting dates of the current and previous periods from each date on the primary axis. This ensures the top axis aligns with the previous period’s data.

Important note:

The custom configuration relies on the specific order of the fields in your Explore results. If you change the order, you’ll need to adjust the series and tooltip configurations accordingly. In our setup, the order is: Current Measure, Current Max Date, Previous Measure, Previous Max Date.

Also, if you are looking to further customize the visualization, take a look at the Chart Config (Source) components of the Edit Chart Config tool. This gives you a good sense of what’s currently set.

Lastly, the Customer Highcharts AI Tool is pretty darn helpful in understanding how to further edit your charts.

Part 3: Further customization

While we’ve provided a solid foundation, you can further enhance this solution:

• Parameterized measure: For even greater flexibility, you could explore parameterizing the measure you want to analyze, allowing users to switch metrics directly from a Dashboard or Look interface.
• More periods: The LookML can be extended to include comparisons for more than just the immediate previous period by adding more UNION ALL statements in the pop_support derived table and adjusting the visualization configuration.

Conclusion: Simple, intuitive, and powerful PoP

This “PoP Simple” method offers a refreshing approach to Period over Period analysis in Looker. By combining a straightforward LookML structure with the flexibility of custom chart configurations, you can create intuitive and insightful visualizations that empower your users to easily identify and understand trends over time, all while keeping the X-axis grounded in actual dates.

A big thank you to Bryan Weber for his ingenuity in crafting the visualization and to Kevin McCarthy for his clever use of the UNION ALL statement to establish the foundation of “PoP Simple.”

We encourage you to try out this method in your Looker environment and experience the simplicity and power of date-centric Period over Period analysis! Let us know in the comments if you have any questions or suggestions for further enhancements.

[yking]

That’s a great solution to be honest.

My only concern was if the data was realtime and you wanted to do the period comparison of today’s data (let’s say until 6PM) with yesterday’s data (until 6PM) only, then this was not producing a great picture. Since it always takes complete days for previous period.

As a workaround, what I did is -

• I tweaked the pop_date with the timestamp_add instead of date_add (ensure that your field is timestamp or add the conversion logic if needed):
  sql: timestamp_add(${view_name.timestamp_raw}, interval ${periods_ago} {% parameter period_offset %});;

• In the filter bar of the explore tab. I selected the ‘pop_date_date’ in hours format instead of date format (number of days that you want * 24)

  

And the final day always shows the comparison for previous day until the hour of the current day. Maybe there’s a better way to do it but since I’ve recently started learning LookML I believe this solved my problem in a very easy manner.

[corgi-looker]

This is a great suggestion @yking and you totally nailed setting this up for hourly vs daily. Thanks for the addition!

[yking]

I seem to have run into a situation here @corgi-looker .
The approach is good to adapt and work well but the biggest challenge comes with the oldest date and its comparison. To give you an example:

I have my data filtered for ‘Last 7 days’ and my graphs and charts are all based on this approach of dynamic PoP. For any given day it shows the previous period comparison completely fine but as soon as we look as the oldest date (in this 7 day window) it shows a clear 0 for previous period because the base filter is configured to select only last 7 days of data(1st - 7th) and the previous period for the foremost date (1st) shows 0 because the data for previous month’s last day is omitted.

This becomes an even bigger problem when I’m not using a graph but only a simple chart to show the contribution on a dimension in the selected time frame and its PoP comparison (as for current period it takes last 7 days and for previous it can only accumulate last 6 days because of the base filter on the dashboard).

Do you have any suggestion to solve this?

[Divya_Ruth_SQR_SHIFT]

This is great! Can I do this in Looker views and use them in Looker Studio using the Looker connector?