Methods for Period Over Period (PoP) Analysis in Looker - Method 7: Arbitrary Period and Directly Previous Period

This post describes the period over period (pop) Method 7: Arbitrary Period and Directly 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 7: Arbitrary Period and Directly Previous Period - Compare Any Period with the Previous Preceding Period of the Same Length

This method enables users to select any date range and compare it to the previous period of the same length. For example, if a user selects a range of 12 days in the past, the data will be compared to 12 days directly preceding that range. This is particularly useful for single value visualization comparisons.

Below is an example of a single value visualization comparison between a custom period and the period of the same length directly preceding it:

Advantages and Disadvantages

Advantages:

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

  When you are applying the filter to a dashboard: If there is another date filter on the dashboard, make sure that the tiles required to update with the PoP analysis listen to the PoP date filter.

Disadvantage:

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

Method

1. Create a date filter field for users to enter a date or date range for a Look or dashboard. See the date_filter field in the LookML example below.
2. Create two hidden fields of type: time to convert the start date and end date entered by the user into raw dates. A raw timeframe will allow flexibility in the interval calculation in the following step. In order to account for the possibility that a user selects "is before" logic in the date filter, the start date and end date fields wrap the filter logic in a CASE statement that will make the start date "2013-01-01" (this date can be a Unix epoch, "1970-01-01", or you can use the first date in your database). The same CASE logic is used when we convert the end date of the filter to account for the user selecting "is on or after" (where the end date is the current date). See the filter_start_date and filter_end_date dimensions in the LookML example below.
3. Create a hidden dimension to calculate the interval, or difference, between the filter start date and end date. See the interval dimension in the LookML example below.
4. Create a hidden dimension to calculate the start date of the previous period by subtracting the interval dimension from the start date. See the previous_start_date dimension in the LookML example below.
5. Create a field to label the two time periods "Selected Period" and "Previous Period" in the user-facing content. The previous period is the period between the previous start date and the start of the selected period (the previous period ends when the selected period begins). See the timeframes dimension in the LookML example below.
   • The logic indicating whether a date is within the selected period or previous period is defined in two dimensions of type: yesno that are then referenced in the timeframes dimension to label the period appropriately. The logic in yesno dimensions uses an existing date field, ${created_date}, and can be reused to create filtered measures. See is_current_period and is_previous_period under For Filtered Measures in the LookML example below.
   • Alternatively, code the logic from the yesno fields directly into the CASE statement in the timeframes dimension.
6. (Optional) Create filtered measures. See selected_period_order_revenue and previous_period_order_revenue under For 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. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.

    ###  Period over Period Method 7: Compare any period with the previous preceding period of the same length

    # Like Method 2, but here we define any date filter (not just current) and
    # compare it to the previous period of the same length - great for single value visualization comparisons

    include: "method1.view.lkml"
    view: pop_previous {
        extends: [order_items]


        filter: date_filter {
        view_label: "_PoP"
        description: "Use this date filter in combination with the timeframes dimension for dynamic date filtering"
        type: date
    }

    dimension_group: filter_start_date {
        hidden: yes
        type: time
        timeframes: [raw,date]
        sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '2013-01-01' ELSE CAST({% date_start date_filter %} AS DATE) END;;
    }

    dimension_group: filter_end_date {
        hidden: yes
        type: time
        timeframes: [raw,date]
        sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE CAST({% date_end date_filter %} AS DATE) END;;
    }

    dimension: interval {
        hidden: yes
        type: number
        sql: datediff(day, ${filter_start_date_raw}, ${filter_end_date_raw});;
    }

    #start date of the previous period
    dimension: previous_start_date {
        hidden: yes
        type: string
        sql: DATEADD(day, - ${interval}, ${filter_start_date_raw});;
    }


    dimension: timeframes {
        view_label: "_PoP"
        type: string
        case: {
        when: {
            sql: ${is_current_period} = true;;
            label: "Selected Period"
        }
        when: {
            sql: ${is_previous_period} = true;;
            label: "Previous Period"
        }
        else: "Not in time period"
        }
    }

    ## For filtered measures


        dimension: is_current_period {
        hidden: yes
        type: yesno
        sql: ${created_date} >= ${filter_start_date_date} AND ${created_date} < ${filter_end_date_date} ;;
        }

        dimension: is_previous_period {
        hidden: yes
        type: yesno
        sql: ${created_date} >= ${previous_start_date} AND ${created_date} < ${filter_start_date_date} ;;
        }

    measure: selected_period_order_revenue {
        view_label: "_PoP"
        type: sum
        sql: ${sale_price} ;;
        filters: [is_current_period: "yes"]
        value_format_name: decimal_1
    }
    measure: previous_period_order_revenue {
        view_label: "_PoP"
        type: sum
        sql: ${sale_price} ;;
        filters: [is_previous_period: "yes"]
        value_format_name: decimal_1
    }

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

    }

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

    explore: pop_previous {
        label: "PoP Method 7: Compare any period with the previous period"
        always_filter: {
          filters: [date_filter]
        }
         sql_always_where: ${timeframes} <>'Not in time period' ;;
      }