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
sqlparameters 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:
Below is an example of a filtered measure based on custom periods made possible by this method:
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
- Create a
filterfield for the first date range. Seefirst_period_filterunder USER FILTERS in the LookML example below. - Create a filter field for the second date range. See
second_period_filterunder USER FILTERS in the LookML example below. - 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_firstanddays_from_start_seconddimensions under HIDDEN HELPER DIMENSIONS in the LookML example below. - 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_perioddimension under DIMENSIONS TO PLOT in the LookML example below. - 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 theperiod_selecteddimension 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. - Add an
always_filterparameter to theexplore, 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_itemsview referenced in the introduction. If you are not using Redshift, be sure to adapt any SQL in thesqlparameters 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"]
}
}
