Methods for Period Over Period (PoP) Analysis in Looker - Method 3: Current Period and Previous Period

This post describes the period over period (pop) Method 3: Current Period and Previous Period highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.

This example uses an order_items table from an e-commerce dataset. The example LookML view below is the basis of this example:

All SQL in the LookML sql parameters in the following examples is specific to the Redshift dialect and will need to be adapted to your database dialect. This open source project provides adapted examples in BigQuery, Snowflake, and MySQL dialects.

    view: order_items {
        sql_table_name: public.order_items ;;


        dimension: id {
            primary_key: yes
            hidden: yes
            type: number
            sql: ${TABLE}.id ;;
        }

        dimension_group: created {
            type: time
            view_label: "_PoP"
            timeframes: [
            raw,
            time,
            hour_of_day,
            date,
            day_of_week,
            day_of_week_index,
            day_of_month,
            day_of_year,
            week,
            week_of_year,
            month,
            month_name,
            month_num,
            quarter,
            year
            ]
            sql: ${TABLE}.created_at ;;
            convert_tz: no
        }

        measure: count {
            label: "Count of order_items"
            type: count
            hidden: yes
        }
        measure: count_orders {
            label: "Count of orders"
            type: count_distinct
            sql: ${order_id} ;;
            hidden: yes
        }

        measure: total_sale_price {
            label: "Total Sales"
            view_label: "_PoP"
            type: sum
            sql: ${sale_price} ;;
            value_format_name: usd
            drill_fields: [created_date]
        }
    }    

Period-over-Period Method 3: Current Period and Previous Period - Custom Choice of Current and Previous Periods with Parameters

This method allows users to select a "previous period," which is a period of the same length immediately preceding the current selected period.

This method uses a similar approach to Method 2: Allow Users to Choose Periods with Parameters; but, instead of using parameters to select the appropriate date timeframe, the logic for the correct previous period is defined using Liquid. See the examples below:

Explore grouped by date, comparing sales in the past month to sales in the previous period of the same length:

Explore using filtered measures, without a pivot on the period to compare:

Advantages and Disadvantages

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).

Method

1. Create a filter field for the user to select the current period. See the current_date_range field in the LookML example below.
   • Apply a sql parameter to the filter to exclude any dates outside the chosen date range.
2. Create a parameter for selecting a previous period type (e.g., previous week, previous year, etc.). See the compare_to parameter in the LookML example below.
3. Create hidden "helper" dimensions for calculating periods:
   • Create a dimension to calculate the number of days in the current period. See the days_in_period dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • Create a dimension to calculate the start and end dates for the previous period. See the period_2_start and period_2_end dimensions under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • Create a dimension to calculate the ordinal value of each day in the current and previous periods (1st day, 2nd day...) to "line up" the two periods. See the day_in_period dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • (Optional) Create a dimension to correctly order the pivot columns. See the order_for_period dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.
   • (Optional) Create a dimension to provide static labels to be used in filtered measures. See the period_filtered_measures under TO CREATE FILTERED MEASURES in the LookML example below.

Next, create the dimensions to be selected in the Explore:

1. Create a dimension_group that will be used as the grouping rows. See the date_in_period dimension_group under DIMENSIONS TO PLOT in the LookML example below.
   • This is constructed from the start date of the current period and the ordinal value of each day in the period.
2. Create a pivot dimension that will group the dates into the current or previous period and provide user-friendly labels. See the period dimension under DIMENSIONS TO PLOT in the LookML example below.
   • If the date is between the current_date_range filter values, then it is in the current period. If the date is between the previous period start and previous period end dates, it is in the previous period.
3. (Optional) To make filtered measures based on these custom periods, use either "last" (for the previous period) or "this" (for the current period) as the filter expression. See current_period_sales and previous_period_sales under TO CREATE FILTERED MEASURES in the LookML example below.
   • An additional measure can be created to calculate percent change period-over-period with the custom period filtered measures. See the sales_pop_change measure under TO CREATE FILTERED MEASURES in the LookML example below.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

    ###  Period over Period Method 3: Custom choice of current and previous periods with parameters

    # Like Method 2, but instead of using parameters to simply select the appropriate date dimension,
    # we will use liquid to define the logic to pick out the correct periods for each selection.


    include: "method1.view.lkml"

    view: pop_parameters {
        extends: [order_items]


        filter: current_date_range {
        type: date
        view_label: "_PoP"
        label: "1. Current Date Range"
        description: "Select the current date range you are interested in. Make sure any other filter on Event Date covers this period, or is removed."
        sql: ${period} IS NOT NULL ;;
        }

        parameter: compare_to {
        view_label: "_PoP"
        description: "Select the templated previous period you would like to compare to. Must be used with Current Date Range filter"
        label: "2. Compare To:"
        type: unquoted
        allowed_value: {
            label: "Previous Period"
            value: "Period"
        }
        allowed_value: {
            label: "Previous Week"
            value: "Week"
        }
        allowed_value: {
            label: "Previous Month"
            value: "Month"
        }
        allowed_value: {
            label: "Previous Quarter"
            value: "Quarter"
        }
        allowed_value: {
            label: "Previous Year"
            value: "Year"
        }
        default_value: "Period"
        view_label: "_PoP"
        }



    ## ------------------ HIDDEN HELPER DIMENSIONS  ------------------ ##

        dimension: days_in_period {
        hidden:  yes
        view_label: "_PoP"
        description: "Gives the number of days in the current period date range"
        type: number
        sql: DATEDIFF(DAY, DATE({% date_start current_date_range %}), DATE({% date_end current_date_range %})) ;;
        }

        dimension: period_2_start {
        hidden:  yes
        view_label: "_PoP"
        description: "Calculates the start of the previous period"
        type: date
        sql:
            {% if compare_to._parameter_value == "Period" %}
            DATEADD(DAY, -${days_in_period}, DATE({% date_start current_date_range %}))
            {% else %}
            DATEADD({% parameter compare_to %}, -1, DATE({% date_start current_date_range %}))
            {% endif %};;
        }

        dimension: period_2_end {
        hidden:  yes
        view_label: "_PoP"
        description: "Calculates the end of the previous period"
        type: date
        sql:
            {% if compare_to._parameter_value == "Period" %}
            DATEADD(DAY, -1, DATE({% date_start current_date_range %}))
            {% else %}
            DATEADD({% parameter compare_to %}, -1, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
            {% endif %};;
        }

        dimension: day_in_period {
        hidden: yes
        description: "Gives the number of days since the start of each period. Use this to align the event dates onto the same axis, the axes will read 1,2,3, etc."
        type: number
        sql:
        {% if current_date_range._is_filtered %}
            CASE
            WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
            THEN DATEDIFF(DAY, DATE({% date_start current_date_range %}), ${created_date}) + 1
            WHEN ${created_date} between ${period_2_start} and ${period_2_end}
            THEN DATEDIFF(DAY, ${period_2_start}, ${created_date}) + 1
            END
        {% else %} NULL
        {% endif %}
        ;;
        }

        dimension: order_for_period {
        hidden: yes
        type: number
        sql:
            {% if current_date_range._is_filtered %}
                CASE
                WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
                THEN 1
                WHEN ${created_date} between ${period_2_start} and ${period_2_end}
                THEN 2
                END
            {% else %}
                NULL
            {% endif %}
            ;;
        }

     ## ------- HIDING FIELDS  FROM ORIGINAL VIEW FILE  -------- ##    

        dimension_group: created {hidden: yes}
        dimension: ytd_only {hidden:yes}
        dimension: mtd_only {hidden:yes}
        dimension: wtd_only {hidden:yes}


    ## ------------------ DIMENSIONS TO PLOT ------------------ ##

        dimension_group: date_in_period {
        description: "Use this as your grouping dimension when comparing periods. Aligns the previous periods onto the current period"
        label: "Current Period"
        type: time
        sql: DATEADD(DAY, ${day_in_period} - 1, DATE({% date_start current_date_range %})) ;;
        view_label: "_PoP"
        timeframes: [
            date,
            hour_of_day,
            day_of_week,
            day_of_week_index,
            day_of_month,
            day_of_year,
            week_of_year,
            month,
            month_name,
            month_num,
            year]
        }


        dimension: period {
        view_label: "_PoP"
        label: "Period"
        description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period' or 'Previous Period'"
        type: string
        order_by_field: order_for_period
        sql:
            {% if current_date_range._is_filtered %}
                CASE
                WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
                THEN 'This {% parameter compare_to %}'
                WHEN ${created_date} between ${period_2_start} and ${period_2_end}
                THEN 'Last {% parameter compare_to %}'
                END
            {% else %}
                NULL
            {% endif %}
            ;;
        }


    ## ---------------------- TO CREATE FILTERED MEASURES ---------------------------- ##

        dimension: period_filtered_measures {
        hidden: yes
        description: "We just use this for the filtered measures"
        type: string
        sql:
            {% if current_date_range._is_filtered %}
                CASE
                WHEN {% condition current_date_range %} ${created_raw} {% endcondition %} THEN 'this'
                WHEN ${created_date} between ${period_2_start} and ${period_2_end} THEN 'last' END
            {% else %} NULL {% endif %} ;;
        }

    # Filtered measures

        measure: current_period_sales {
        view_label: "_PoP"
        type: sum
        sql: ${sale_price};;
        filters: [period_filtered_measures: "this"]
        }

        measure: previous_period_sales {
        view_label: "_PoP"
        type: sum
        sql: ${sale_price};;
        filters: [period_filtered_measures: "last"]
        }

        measure: sales_pop_change {
        view_label: "_PoP"
        label: "Total Sales period-over-period % change"
        type: number
        sql: CASE WHEN ${current_period_sales} = 0
                THEN NULL
                ELSE (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales} ,0)) - 1 END ;;
        value_format_name: percent_2
        }

    }

    # ---------- EXPLORE ---------- #

    explore: pop_parameters {
        label: "PoP Method 3: Custom choice of current and previous periods with parameters"
        always_filter: {
          filters: [current_date_range: "6 months", compare_to: "Year" ]
            }
    }