Looker will not be updating this content, nor guarantees that everything is up-to-date.
This post describes the period over period (pop) Method 3: Current Period and 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]
}
}
This method allows users to select a "previous period," which is a period of the same length immediately preceding the current selected period.
This method uses a similar approach to Method 2: Allow Users to Choose Periods with Parameters; but, instead of using parameters to select the appropriate date timeframe, the logic for the correct previous period is defined using Liquid. See the examples below:
Explore grouped by date, comparing sales in the past month to sales in the previous period of the same length:
Explore using filtered measures, without a pivot on the period to compare:
Advantages:
Disadvantages:
filter
field for the user to select the current period. See the current_date_range
field in the LookML example below. sql
parameter to the filter to exclude any dates outside the chosen date range.parameter
for selecting a previous period type (e.g., previous week, previous year, etc.). See the compare_to
parameter in the LookML example below.days_in_period
dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.period_2_start
and period_2_end
dimensions under HIDDEN HELPER DIMENSIONS in the LookML example below.day_in_period
dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.order_for_period
dimension under HIDDEN HELPER DIMENSIONS in the LookML example below.period_filtered_measures
under TO CREATE FILTERED MEASURES in the LookML example below.Next, create the dimensions to be selected in the Explore:
dimension_group
that will be used as the grouping rows. See the date_in_period
dimension_group under DIMENSIONS TO PLOT in the LookML example below. period
dimension under DIMENSIONS TO PLOT in the LookML example below. current_date_range
filter values, then it is in the current period. If the date is between the previous period start and previous period end dates, it is in the previous period.current_period_sales
and previous_period_sales
under TO CREATE FILTERED MEASURES in the LookML example below. sales_pop_change
measure under TO CREATE FILTERED MEASURES in the LookML example below.Below is an example view file with the fields referenced above:
This example view 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 3: Custom choice of current and previous periods with parameters
# Like Method 2, but instead of using parameters to simply select the appropriate date dimension,
# we will use liquid to define the logic to pick out the correct periods for each selection.
include: "method1.view.lkml"
view: pop_parameters {
extends: [order_items]
filter: current_date_range {
type: date
view_label: "_PoP"
label: "1. Current Date Range"
description: "Select the current date range you are interested in. Make sure any other filter on Event Date covers this period, or is removed."
sql: ${period} IS NOT NULL ;;
}
parameter: compare_to {
view_label: "_PoP"
description: "Select the templated previous period you would like to compare to. Must be used with Current Date Range filter"
label: "2. Compare To:"
type: unquoted
allowed_value: {
label: "Previous Period"
value: "Period"
}
allowed_value: {
label: "Previous Week"
value: "Week"
}
allowed_value: {
label: "Previous Month"
value: "Month"
}
allowed_value: {
label: "Previous Quarter"
value: "Quarter"
}
allowed_value: {
label: "Previous Year"
value: "Year"
}
default_value: "Period"
view_label: "_PoP"
}
## ------------------ HIDDEN HELPER DIMENSIONS ------------------ ##
dimension: days_in_period {
hidden: yes
view_label: "_PoP"
description: "Gives the number of days in the current period date range"
type: number
sql: DATEDIFF(DAY, DATE({% date_start current_date_range %}), DATE({% date_end current_date_range %})) ;;
}
dimension: period_2_start {
hidden: yes
view_label: "_PoP"
description: "Calculates the start of the previous period"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -${days_in_period}, DATE({% date_start current_date_range %}))
{% else %}
DATEADD({% parameter compare_to %}, -1, DATE({% date_start current_date_range %}))
{% endif %};;
}
dimension: period_2_end {
hidden: yes
view_label: "_PoP"
description: "Calculates the end of the previous period"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -1, DATE({% date_start current_date_range %}))
{% else %}
DATEADD({% parameter compare_to %}, -1, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
{% endif %};;
}
dimension: day_in_period {
hidden: yes
description: "Gives the number of days since the start of each period. 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
END
{% else %} NULL
{% endif %}
;;
}
dimension: order_for_period {
hidden: yes
type: number
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
END
{% else %}
NULL
{% endif %}
;;
}
## ------- HIDING FIELDS FROM ORIGINAL VIEW FILE -------- ##
dimension_group: created {hidden: yes}
dimension: ytd_only {hidden:yes}
dimension: mtd_only {hidden:yes}
dimension: wtd_only {hidden:yes}
## ------------------ DIMENSIONS TO PLOT ------------------ ##
dimension_group: date_in_period {
description: "Use this as your grouping dimension when comparing periods. Aligns the previous periods onto the current period"
label: "Current Period"
type: time
sql: DATEADD(DAY, ${day_in_period} - 1, DATE({% date_start current_date_range %})) ;;
view_label: "_PoP"
timeframes: [
date,
hour_of_day,
day_of_week,
day_of_week_index,
day_of_month,
day_of_year,
week_of_year,
month,
month_name,
month_num,
year]
}
dimension: period {
view_label: "_PoP"
label: "Period"
description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period' or 'Previous Period'"
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 %}'
END
{% else %}
NULL
{% endif %}
;;
}
## ---------------------- TO CREATE FILTERED MEASURES ---------------------------- ##
dimension: period_filtered_measures {
hidden: yes
description: "We just use this for the filtered measures"
type: string
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %} THEN 'this'
WHEN ${created_date} between ${period_2_start} and ${period_2_end} THEN 'last' END
{% else %} NULL {% endif %} ;;
}
# Filtered measures
measure: current_period_sales {
view_label: "_PoP"
type: sum
sql: ${sale_price};;
filters: [period_filtered_measures: "this"]
}
measure: previous_period_sales {
view_label: "_PoP"
type: sum
sql: ${sale_price};;
filters: [period_filtered_measures: "last"]
}
measure: sales_pop_change {
view_label: "_PoP"
label: "Total Sales period-over-period % change"
type: number
sql: CASE WHEN ${current_period_sales} = 0
THEN NULL
ELSE (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales} ,0)) - 1 END ;;
value_format_name: percent_2
}
}
# ---------- EXPLORE ---------- #
explore: pop_parameters {
label: "PoP Method 3: Custom choice of current and previous periods with parameters"
always_filter: {
filters: [current_date_range: "6 months", compare_to: "Year" ]
}
}