Methods for Period Over Period (PoP) Analysis in Looker - Method 5: Current Period and Any Arbitrary Period

Looker Tips & Resources Technical Tips & Tricks
1

This post describes the period over period (pop) Method 5: Current Period and Any Arbitrary 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 5: Current Period and Any Arbitrary Period - Compare Current Period with Another Arbitrary Period

This method is an extension of Method 3: Custom Choice of Current and Previous Periods with Parameters. Please be sure to familiarize yourself with Method 3 before moving forward with implementing Method 5.

Sometimes you don’t want to compare the current period to exactly the previous period. Adding another filter to Method 3 allows users to compare the current period with any other arbitrary date range.

Below is an example looking at the current period against a custom previous date range:

Advantages and Disadvantages

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

Method

  1. Create a filter field for the user to select the previous date range they want to compare against. See the previous_date_range filter field in the LookML example below.
  2. Create hidden dimensions to calculate the start and end of the user-selected previous period. See the period_2_start and period_2_end dimensions in the LookML example below.

LookML Example

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

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

###  Period over Period Method 5: Compare current period with another arbitrary period

    # Like Method 3, but allows you to compare the current period with any other arbitrary date range period

    include: "method3.view.lkml"

    ## This extended version allows the user to also choose a custom date range for the previous period

    view: pop_parameters_with_custom_range {
        extends: [pop_parameters]

        # custom date range
        filter: previous_date_range {
        type: date
        view_label: "_PoP"
        label: "2a. Previous Date Range (Custom):"
        description: "Select a custom previous period you would like to compare to. Must be used with Current Date Range filter."
        }

        parameter: compare_to {label: "2b. Compare To:"}
        dimension_group: date_in_period {hidden:yes}

        dimension: period_2_start {
        view_label: "_PoP"
        description: "Calculates the start of the previous period"
        type: date
        sql:
            {% if compare_to._in_query %}
                {% 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 %}
            {% else %}
                {% date_start previous_date_range %}
            {% endif %};;
        hidden:  yes
        }

        dimension: period_2_end {
        hidden:  yes
        view_label: "_PoP"
        description: "Calculates the end of the previous period"
        type: date
        sql:
            {% if compare_to._in_query %}
                {% 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 %}
            {% else %}
                {% date_end previous_date_range %}
            {% endif %};;
        }
    }

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

    explore: pop_parameters_with_custom_range {
        label: "PoP Method 5: Compare current period with another arbitrary period"
        always_filter: {
          filters: [current_date_range: "1 month", previous_date_range: "2 months ago for 2 days"]
        }
      }