Period-Over-Period Implementations

This period_over_period project implements popular Looker period-over-period methods. The functionality can be replicated by copying LookML code from the views specified for each method into views in the target project. Once copied, these views can be customized for dimensions and measures present there and intended for period over period reporting. The LookML code can be added to existing views or left in stand-alone views and be optionally joined with existing explores.

The instructions in the Implementation sections below will utilize stand-alone views copied directly from the method views.

 

Available Methods

The following methods have been implemented in this project for easy "lift and shift" into client projects. Selections can be made according to required period comparison and complexity preference.  Instructions for implementing each are detailed below.

Methods for Period Over Period (PoP) Analysis in Looker - Molly Lippsett 2022.

 

Method 1 - Any Two Native Timeframes

Advantage:

• Users can self-serve PoP analyses in an Explore. Developing additional fields (unless you create a yesno LookML field on which to filter, as in the dashboard example) or Liquid implementation is not required.

Disadvantages:

• The type of comparison (e.g., year-over-year, month-over-month) cannot be changed without choosing different fields in an Explore.
• This can be time-consuming and potentially confusing for users to do themselves in an Explore and is impossible for users without explore permissions.
• Importantly, this means it is impossible to control the PoP analysis using dashboard filters, as they can only be applied to a single field.
• This method does not easily support filtered measures. For example, logic such as "% change vs previous period" must be achieved with a table calculation, rather than with a measure.

Quick Implementation:

1. Basic Method 1 functionality is available in standard Looker by pivoting on time period.
2. The method1.view contains additional filters and a parameter to limit results to the current date position in the focus period.
3. Create a method1.view file in your target project and paste in the method1 LookML code.
4. Update the include: and extends: statements to appropriate values in your target project.
5. In the dimension and measures, update the SQL: statements and names fitting your project.

Sample Dashboard: Method 1

 

Method 2 - Any Two Native Timeframes (with Liquid)

 

Advantages:

• Minimal modeling is required.
• View-only users can manipulate the analysis on a dashboard.
• The user experience is simplified and only requires changing filter values, rather than changing Explore fields.

Disadvantages:

• Filtered measures are incompatible with this method.

• Custom time periods (anything that is not a dimension_group timeframe) are not supported.

Quick Implementation:

1. Create a method1.view file in your target project and paste in the method1 LookML code if you have not already.
2. In the dimension and measures in the method1 file, update the SQL: statements and names fitting your project.
3. Create a method2.view file in your target project and paste in the method2 LookML code.
4. Update the include: and extends: statements in the method2 view to reflect location of method1 code if you have renamed or copied into another view.

Sample Dashboard: Method 2

 

Method 3 - Current Period and Previous Period

Advantages:

• Performs the same function as Method 2: Allow Users to Choose Periods with Parameters.
• Supports filtered measures.
• Provides granular control of period definition without requiring too many fields, which may confuse the user.

Disadvantages:

• Requires complex LookML modeling, including Liquid implementation.
• Overlapping periods are not supported (this requires a join).

• Only a single date field may be used (e.g., the same functionality for other date fields requires replicating all the code, per date field).

Quick Implementation:

1. Create a method1.view file in your target project and paste in the method1 LookML code if you have not already.
2. In the dimension and measures in the method1 file, update the SQL: statements and names fitting your project.
3. Create a method3.view file in your target project and paste in the method3 LookML code.
4. Update the include: and extends: statements in the method3 view to reflect location of method1 code if you have renamed or copied into another view.

Sample Dashboard: Method 3

 

Method 4 - Current Period and Many Previous Periods

Advantages:

• Performs the same function as Method 3: Custom Choice of Current and Previous Periods with Parameters.
• Can accommodate more than two period comparisons in a PoP analysis.

Disadvantages:

• Requires complex LookML modeling, including Liquid implementation.
• The developer must decide how many periods to create parameter values for, which can potentially limit user analysis.
• Overlapping periods are not supported (this requires a join).

• Only a single date field may be used (e.g., the same functionality for other dates requires replicating all the code, per date).

Quick Implementation:

1. Create a method1.view file in your target project and paste in the method1 LookML code if you have not already.
2. In the dimension and measures in the method1 file, update the SQL: statements and names fitting your project.
3. Create a method3.view file in your target project and paste in the method3 LookML code if you have not already.
4. Update the include: and extends: statements in the method3 view to reflect location of method1 code if you have renamed or copied into another view.
5. Create a method4.view file in your target project and paste in the method4 LookML code if you have not already.
6. Update the include: and extends: statements in the method4 view to reflect location of method3 code if you have renamed or copied into another view.

Sample Dashboard: Method 4

 

Method 5 - Current Period and Any Arbitrary Period

Advantages:

• Performs the same function as Method 3: Custom Choice of Current and Previous Periods with Parameters.
• A custom date range can be compared against the current period chosen two arbitrary date ranges can be plotted side by side.

Disadvantages:

• Requires complex LookML modeling, including Liquid implementation.
• The developer must decide how many custom periods to allow, which can potentially limit user analysis. The LookML example below only allows for one custom date range to compare against the current period.
• Overlapping periods are not supported (this requires a join).

• Only a single date field may be used (e.g., the same functionality for other dates requires replicating all the code, per date).

Quick Implementation:

1. Create a method1.view file in your target project and paste in the method1 LookML code if you have not already.
2. In the dimension and measures in the method1 file, update the SQL: statements and names fitting your project.
3. Create a method3.view file in your target project and paste in the method3 LookML code if you have not already.
4. Update the include: and extends: statements in the method3 view to reflect location of method1 code if you have renamed or copied into another view.
5. Create a method5.view file in your target project and paste in the method5 LookML code if you have not already.
6. Update the include: and extends: statements in the method5 view to reflect location of method3 code if you have renamed or copied into another view.

Sample Dashboard: Method 5

 

Method 6 - Any Two Arbitrary Periods

Advantage:

• This type of analysis is familiar to Google Analytics users and is a great choice to use for a similar use case.

Disadvantages:

• Both comparison date ranges must be set manually, which may seem slower and more complex to users who expect a simpler PoP analysis.
• There are other additional nuances that may potentially frustrate users:
• The first period must occur before the second period.
• An additional filter is required to ensure that the 'First Period' and 'Second Period' labels are not null.

• Overlapping periods are not supported (this requires a join).

Quick Implementation:

1. Create a method1.view file in your target project and paste in the method1 LookML code if you have not already.
2. In the dimension and measures in the method1 file, update the SQL: statements and names fitting your project.
3. Create a method6.view file in your target project and paste in the method6 LookML code.
4. Update the include: and extends: statements in the method6 view to reflect location of method1 code if you have renamed or copied into another view.

Sample Dashboard: Method 6

 

Method 7: Arbitrary Period and Directly Previous Period - Compare Any Period with the Previous Preceding Period of the Same Length

Advantage:

• The previous period dynamically matches the interval of the selected date range.
• Limited Liquid is required in the implementation.
• Filtered measures are supported.
• The date range filter can be used on dashboards so that view-only users can manipulate the filter values.

Disadvantages:

• This method does not allow for gaps between periods; only directly preceding periods can be compared.

Quick Implementation:

1. Create a method1.view file in your target project and paste in the method1 LookML code if you have not already.
2. In the dimension and measures in the method1 file, update the SQL: statements and names fitting your project.
3. Create a method7.view file in your target project and paste in the method7 LookML code.
4. Update the include: and extends: statements in the method7 view to reflect location of method1 code if you have renamed or copied into another view.

Sample Dashboard: Method 7

 

Citations

LookML code in this project has been developed and enhanced by Bytecode IO developers using base code from:

-Molly Lippsett 2022, Methods for Period Over Period (PoP) Analysis in Looker,[LookML Example],https://community.looker.com/technical-tips-tricks-1021/methods-for-period-over-period-pop-analysis-in-looker-30823

 -Llooker 2022, Lookml period over period analysis in different dialects, [views],https://github.com/llooker/period_over_period_analysis