Methods for Period Over Period (PoP) Analysis in Looker - Method 6: Any Two Arbitrary Periods

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

This post describes the period over period (pop) Method 6: Any Two Arbitrary Periods 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 6: Any Two Arbitrary Periods - Compare Two Arbitrary Date Ranges

Sometimes an analysis requires a comparison between two arbitrary periods, not including the current period. This is relevant for use cases such as web analytics, where, for example, the number of registrations for an upcoming webinar needs to be compared to a previous webinar from six months ago. The challenge is that the date ranges might have a different number of days, so the periods need to be aligned based on their start day.

Below is an example of a period-over-period analysis with custom periods:

81ecc2a3-93b5-4fb4-885b-cf4e01fab3c4.png

Below is an example of a filtered measure based on custom periods made possible by this method:

d23b0930-cef7-4f54-940c-e92f98c95f25.png

Advantages and Disadvantages

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

Method

  1. Create a filter field for the first date range. See first_period_filter under USER FILTERS in the LookML example below.
  2. Create a filter field for the second date range. See second_period_filter under USER FILTERS in the LookML example below.
  3. Create two hidden dimensions to calculate the number of days between the start dates of the two filters and a relevant date in the dataset. See the days_from_start_first and days_from_start_second dimensions under HIDDEN HELPER DIMENSIONS in the LookML example below.
  4. Create a dimension that checks the grouping date against the start of the second and first filter periods — this ensures that the two periods are lined up based on their start date. See the days_from_first_period dimension under DIMENSIONS TO PLOT in the LookML example below.
  5. Create a dimension that uses the hidden dimensions from step 2 to label each day as either 'First Period' or 'Second Period' for the Explore analysis pivot. See the period_selected dimension under DIMENSIONS TO PLOT in the LookML example below.
    Labeling won't work if your periods overlap. There are no joins, so each row can only be in the current or the previous period.
  6. Add an always_filter parameter to the explore, set to filter the pivot field with the condition -NULL. This ensures that there will be no results outside the custom date ranges specified.

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 6: Compare two arbitrary date ranges

# Like Method 5, but allowing arbitrary definition of the 'current' period
# provides functionality like Google Analytics, which allows you to compare two arbitrary date ranges


include: "method1.view.lkml"

view: pop_arbitrary {
extends: [order_items]


## ------------------ USER FILTERS ------------------ ##

filter: first_period_filter {
view_label: "_PoP"
group_label: "Arbitrary Period Comparisons"
description: "Choose the first date range to compare against. This must be before the second period"
type: date
}

filter: second_period_filter {
view_label: "_PoP"
group_label: "Arbitrary Period Comparisons"
description: "Choose the second date range to compare to. This must be after the first period"
type: date
}

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

dimension: days_from_start_first {
view_label: "_PoP"
hidden: yes
type: number
sql: DATEDIFF('day', {% date_start first_period_filter %}, ${created_date}) ;;
}

dimension: days_from_start_second {
view_label: "_PoP"
hidden: yes
type: number
sql: DATEDIFF('day', {% date_start second_period_filter %}, ${created_date}) ;;
}

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

dimension: days_from_first_period {
view_label: "_PoP"
description: "Select for Grouping (Rows)"
group_label: "Arbitrary Period Comparisons"
type: number
sql:
CASE
WHEN ${days_from_start_second} >= 0
THEN ${days_from_start_second}
WHEN ${days_from_start_first} >= 0
THEN ${days_from_start_first}
END;;
}


dimension: period_selected {
view_label: "_PoP"
group_label: "Arbitrary Period Comparisons"
label: "First or second period"
description: "Select for Comparison (Pivot)"
type: string
sql:
CASE
WHEN {% condition first_period_filter %}${created_raw} {% endcondition %}
THEN 'First Period'
WHEN {% condition second_period_filter %}${created_raw} {% endcondition %}
THEN 'Second Period'
END ;;
}

## Filtered measures

measure: current_period_sales {
view_label: "_PoP"
type: sum
sql: ${sale_price};;
filters: [period_selected: "Second Period"]
}

measure: previous_period_sales {
view_label: "_PoP"
type: sum
sql: ${sale_price};;
filters: [period_selected: "First Period"]
}

measure: sales_pop_change {
view_label: "_PoP"
label: "Total sales period-over-period % change"
type: number
sql: (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales} ,0)) - 1 ;;
value_format_name: percent_2
}

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

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

explore: pop_arbitrary {
label: "PoP Method 6: Compare two arbitrary date ranges"
always_filter: {
filters: [first_period_filter: "NOT NULL", second_period_filter: "NOT NULL", period_selected:"-NULL"]
}
}
Comments
Lausanne
New Member

Hi there!

For me, the code doesn’t work. I think it has something to do with the fact that my input for the date range filters is changed into a timestamp rather than a date (since the error message that I receive is that you can’t compare a date to a timestamp when applying the “<=” or “=” operator). I tried solving it with a format_date function, but unfortunately without any luck. Someone who can help me?

Thanks 🙂

Version history
Last update:
‎06-23-2022 10:31 AM
Updated by: