Methods for PoP Analysis in Looker - Method 2: Any Two Native Timeframes (with Liquid)

Looker Tips & Resources Technical Tips & Tricks
1

This post describes the period over period (pop) Method 2: Any Two Native Timeframes (with Liquid) 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 2: Any Two Native Timeframes (with Liquid) - Allow Users to Choose Periods with Parameters

The native implementation of Method 1: Use Looker’s Native Date Dimension Groups may not work out-of-the-box for many use cases. For instance, you may need to allow view-only users to change the analysis by using dashboard filters, or you may need to offer business users a more guided Explore experience.

This additional functionality is possible with interactive dimensions that users can manipulate with parameter filters to change the desired comparisons.

Here is an example grouped by month, year-over-year:

Here is an example grouped by day of month, month-over-month:

Advantages and Disadvantages

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.

Method

  1. Create a parameter to define options for dimension grouping (e.g., group by day, week, month). See the choose_breakdown parameter in the LookML example below.
  2. Create another parameter for selecting a pivot dimension (e.g., pivot by month, year, etc.). See the choose_comparison parameter in the LookML below.
  3. Create dimensions that change according to each parameter value, and use Liquid “if” statements to define the timeframe to use for each parameter value selection. See the pop_row and pop_pivot dimensions in the LookML below. Pro-tips:
    • Adding a label_from_parameter parameter ensures that the resulting visualization will be labeled according to the chosen parameter values.
    • Visualizations can be sorted according to the selected dimension value. Create hidden dimensions to sort month and day names by their numerical or index counterparts, and then use the order_by_field parameter to reference the sort-by dimensions within the interactive dimensions. See the sort_by1 and sort_by2 dimensions 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 2: Allow users to choose periods with parameters

    include: "method1.view.lkml"

    view: pop_simple {
        extends: [order_items]

        parameter: choose_breakdown {
        label: "Choose Grouping (Rows)"
        view_label: "_PoP"
        type: unquoted
        default_value: "Month"
        allowed_value: {label: "Month Name" value:"Month"}
        allowed_value: {label: "Day of Year" value: "DOY"}
        allowed_value: {label: "Day of Month" value: "DOM"}
        allowed_value: {label: "Day of Week" value: "DOW"}
        allowed_value: {value: "Date"}
        }

        parameter: choose_comparison {
        label: "Choose Comparison (Pivot)"
        view_label: "_PoP"
        type: unquoted
        default_value: "Year"
        allowed_value: {value: "Year" }
        allowed_value: {value: "Month"}
        allowed_value: {value: "Week"}
        }

        dimension: pop_row  {
        view_label: "_PoP"
        label_from_parameter: choose_breakdown
        type: string
        order_by_field: sort_hack1 # Important
        sql:
        {% if choose_breakdown._parameter_value == 'Month' %} ${created_month_name}
        {% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
        {% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
        {% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week}
        {% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
        {% else %}NULL{% endif %} ;;
        }

        dimension: pop_pivot {
        view_label: "_PoP"
        label_from_parameter: choose_comparison
        type: string
        order_by_field: sort_hack2 # Important
        sql:
        {% if choose_comparison._parameter_value == 'Year' %} ${created_year}
        {% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_name}
        {% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
        {% else %}NULL{% endif %} ;;
        }

        # These dimensions are just to make sure the dimensions sort correctly
        dimension: sort_by1 {
        hidden: yes
        type: number
        sql:
        {% if choose_breakdown._parameter_value == 'Month' %} ${created_month_num}
        {% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
        {% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
        {% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week_index}
        {% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
        {% else %}NULL{% endif %} ;;
        }

        dimension: sort_by2 {
        hidden: yes
        type: string
        sql:
        {% if choose_comparison._parameter_value == 'Year' %} ${created_year}
        {% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_num}
        {% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
        {% else %}NULL{% endif %} ;;
        }
    }

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

    explore: pop_simple {
        label: "PoP Method 2: Allow users to choose periods with parameters"
        always_filter: {
          filters: [choose_comparison, choose_breakdown]
      }
Methods for Period Over Period (PoP) Analysis in Looker