Looker will not be updating this content, nor guarantees that everything is up-to-date.
This post describes the period over period (pop) Method 4: Current Period and Many Previous 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]
}
}
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 4.
Sometimes you may want to compare more than just the current and previous periods. By adding a new parameter to choose the number of periods to compare, we can allow this; however, it does add a lot of verbosity to the model.
Below is an example looking at the current and previous three periods:
Advantages:
Disadvantages:
comparison_periods
parameter in the LookML example below.allowed_value
subparameters — one for the start of each period, and one for the end of each period. See the period_3_start
, period_3_end
, period_4_start
, and period_4_end
dimensions below. Period two is defined in the extended view. Seeperiod_2_start
andperiod_2_end
under HIDDEN HELPER DIMENSIONS in the Method 3 LookML example.
period
dimension in the LookML example below according to how many periods are available to the user for comparison. This field displays the correct label, depending on the number of periods selected by the user.order_for_period
dimension in the LookML example below.comparison_periods
in the LookML example below) in a sql_always_where
in the Explore. See the pop_parameters_multi_period
`explore` in the LookML example below.day_in_period
dimension in the LookML example below.Below is an example view file with the fields referenced above:
This example view extends thepop_parameters
view referenced in Method 3, which extends theorder_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 thesql
parameters to your database dialect.
### Period over Period Method 4: Compare multiple templated periods
# Like Method 3, but expanded to compare more than two periods
include: "method3.view.lkml"
# This extended version allows you to choose multiple periods (this can also work in conjunction with the custom range version, or separately)
view: pop_parameters_multi_period {
extends: [pop_parameters]
parameter: comparison_periods {
view_label: "_PoP"
label: "3. Number of Periods"
description: "Choose the number of periods you would like to compare."
type: unquoted
allowed_value: {
label: "2"
value: "2"
}
allowed_value: {
label: "3"
value: "3"
}
allowed_value: {
label: "4"
value: "4"
}
default_value: "2"
}
dimension: period_3_start {
view_label: "_PoP"
description: "Calculates the start of 2 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -(2 * ${days_in_period}), DATE({% date_start current_date_range %}))
{% else %}
DATEADD({% parameter compare_to %}, -2, DATE({% date_start current_date_range %}))
{% endif %};;
hidden: yes
}
dimension: period_3_end {
view_label: "_PoP"
description: "Calculates the end of 2 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -1, ${period_2_start})
{% else %}
DATEADD({% parameter compare_to %}, -2, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
{% endif %};;
hidden: yes
}
dimension: period_4_start {
view_label: "_PoP"
description: "Calculates the start of 4 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -(3 * ${days_in_period}), DATE({% date_start current_date_range %}))
{% else %}
DATEADD({% parameter compare_to %}, -3, DATE({% date_start current_date_range %}))
{% endif %};;
hidden: yes
}
dimension: period_4_end {
view_label: "_PoP"
description: "Calculates the end of 4 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -1, ${period_2_start})
{% else %}
DATEADD({% parameter compare_to %}, -3, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
{% endif %};;
hidden: yes
}
dimension: period {
view_label: "_PoP"
label: "Period"
description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period', 'Previous Period' or '3 Periods Ago'"
type: string
order_by_field: order_for_period
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
THEN 'This {% parameter compare_to %}'
WHEN ${created_date} between ${period_2_start} and ${period_2_end}
THEN 'Last {% parameter compare_to %}'
WHEN ${created_date} between ${period_3_start} and ${period_3_end}
THEN '2 {% parameter compare_to %}s Ago'
WHEN ${created_date} between ${period_4_start} and ${period_4_end}
THEN '3 {% parameter compare_to %}s Ago'
END
{% else %}
NULL
{% endif %}
;;
}
dimension: order_for_period {
hidden: yes
view_label: "Comparison Fields"
label: "Period"
type: string
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
THEN 1
WHEN ${created_date} between ${period_2_start} and ${period_2_end}
THEN 2
WHEN ${created_date} between ${period_3_start} and ${period_3_end}
THEN 3
WHEN ${created_date} between ${period_4_start} and ${period_4_end}
THEN 4
END
{% else %}
NULL
{% endif %}
;;
}
dimension: day_in_period {
description: "Gives the number of days since the start of each periods. Use this to align the event dates onto the same axis, the axes will read 1,2,3, etc."
type: number
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
THEN DATEDIFF(DAY, DATE({% date_start current_date_range %}), ${created_date}) + 1
WHEN ${created_date} between ${period_2_start} and ${period_2_end}
THEN DATEDIFF(DAY, ${period_2_start}, ${created_date}) + 1
WHEN ${created_date} between ${period_3_start} and ${period_3_end}
THEN DATEDIFF(DAY, ${period_3_start}, ${created_date}) + 1
WHEN ${created_date} between ${period_4_start} and ${period_4_end}
THEN DATEDIFF(DAY, ${period_4_start}, ${created_date}) + 1
END
{% else %} NULL
{% endif %}
;;
hidden: yes
}
}
# ---------- EXPLORE ---------- #
explore: pop_parameters_multi_period {
label: "PoP Method 4: Compare multiple templated periods"
extends: [pop_parameters]
sql_always_where:
{% if pop_parameters_multi_period.current_date_range._is_filtered %} {% condition pop_parameters_multi_period.current_date_range %} ${created_raw} {% endcondition %}
{% if pop_parameters_multi_period.previous_date_range._is_filtered or pop_parameters_multi_period.compare_to._in_query %}
{% if pop_parameters_multi_period.comparison_periods._parameter_value == "2" %}
or ${created_raw} between ${period_2_start} and ${period_2_end}
{% elsif pop_parameters_multi_period.comparison_periods._parameter_value == "3" %}
or ${created_raw} between ${period_2_start} and ${period_2_end}
or ${created_raw} between ${period_3_start} and ${period_3_end}
{% elsif pop_parameters_multi_period.comparison_periods._parameter_value == "4" %}
or ${created_raw} between ${period_2_start} and ${period_2_end}
or ${created_raw} between ${period_3_start} and ${period_3_end} or ${created_raw} between ${period_4_start} and ${period_4_end}
{% else %} 1 = 1
{% endif %}
{% endif %}
{% else %} 1 = 1
{% endif %};;
}