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]
}
}
Period-over-Period Method 4: Current Period and Many Previous Periods - Compare Multiple Templated Periods
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 and Disadvantages
Advantages:
- Performs the same function as Method 3: Custom Choice of Current and Previous Periods with Parameters.
- Can accommodate more than two period comparisons in a PoP analysis.
Disadvantages:
- Requires complex LookML modeling, including Liquid implementation.
- The developer must decide how many periods to create parameter values for, which can potentially limit user analysis.
- Overlapping periods are not supported (this requires a join).
- Only a single date field may be used (e.g., the same functionality for other dates requires replicating all the code, per date).
Method
- Create a parameter with allowed values to choose the number of periods to compare. See the
comparison_periodsparameter in the LookML example below. - Create two hidden dimensions to calculate the periods for each of the
allowed_valuesubparameters — one for the start of each period, and one for the end of each period. See theperiod_3_start,period_3_end,period_4_start, andperiod_4_enddimensions below.Period two is defined in the extended view. See
period_2_startandperiod_2_endunder HIDDEN HELPER DIMENSIONS in the Method 3 LookML example. - Adjust the
perioddimension 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. - Create a hidden dimension to order periods correctly by numerical value (e.g., period 1, period 2, period 3). See the
order_for_perioddimension in the LookML example below. - Apply the parameter (
comparison_periodsin the LookML example below) in asql_always_wherein the Explore. See thepop_parameters_multi_period`explore` in the LookML example below. - (Optional) Similar to Method 2, create additional (hidden) dimensions to correctly sort strings, such as month and day of week names, by their numerical or index counterparts. See
day_in_perioddimension in the LookML example below. - (Optional) New filtered measures can be created for each possible previous period. See the examples under TO CREATE FILTERED MEASURES in the Method 3 LookML example.
LookML Example
Below is an example view file with the fields referenced above:
This example view extends thepop_parametersview referenced in Method 3, which extends theorder_itemsview referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in thesqlparameters 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 %};;
}