Looker will not be updating this content, nor guarantees that everything is up-to-date.
This post describes the period over period (pop) Method 2: Any Two Native Timeframes (with Liquid) 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]
}
}
The native implementation of Method 1: Use Looker's Native Date Dimension Groups may not work out-of-the-box for many use cases. For instance, you may need to allow view-only users to change the analysis by using dashboard filters, or you may need to offer business users a more guided Explore experience.
This additional functionality is possible with interactive dimensions that users can manipulate with parameter
filters to change the desired comparisons.
Here is an example grouped by month, year-over-year:
Here is an example grouped by day of month, month-over-month:
Advantages:
Disadvantages:
dimension_group
timeframe
) are not supported.choose_breakdown
parameter in the LookML example below.choose_comparison
parameter in the LookML below.pop_row
and pop_pivot
dimensions in the LookML below. Pro-tips: label_from_parameter
parameter ensures that the resulting visualization will be labeled according to the chosen parameter values.order_by_field
parameter to reference the sort-by dimensions within the interactive dimensions. See the sort_by1
and sort_by2
dimensions 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 2: Allow users to choose periods with parameters
include: "method1.view.lkml"
view: pop_simple {
extends: [order_items]
parameter: choose_breakdown {
label: "Choose Grouping (Rows)"
view_label: "_PoP"
type: unquoted
default_value: "Month"
allowed_value: {label: "Month Name" value:"Month"}
allowed_value: {label: "Day of Year" value: "DOY"}
allowed_value: {label: "Day of Month" value: "DOM"}
allowed_value: {label: "Day of Week" value: "DOW"}
allowed_value: {value: "Date"}
}
parameter: choose_comparison {
label: "Choose Comparison (Pivot)"
view_label: "_PoP"
type: unquoted
default_value: "Year"
allowed_value: {value: "Year" }
allowed_value: {value: "Month"}
allowed_value: {value: "Week"}
}
dimension: pop_row {
view_label: "_PoP"
label_from_parameter: choose_breakdown
type: string
order_by_field: sort_hack1 # Important
sql:
{% if choose_breakdown._parameter_value == 'Month' %} ${created_month_name}
{% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
{% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
{% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week}
{% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
{% else %}NULL{% endif %} ;;
}
dimension: pop_pivot {
view_label: "_PoP"
label_from_parameter: choose_comparison
type: string
order_by_field: sort_hack2 # Important
sql:
{% if choose_comparison._parameter_value == 'Year' %} ${created_year}
{% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_name}
{% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
{% else %}NULL{% endif %} ;;
}
# These dimensions are just to make sure the dimensions sort correctly
dimension: sort_by1 {
hidden: yes
type: number
sql:
{% if choose_breakdown._parameter_value == 'Month' %} ${created_month_num}
{% elsif choose_breakdown._parameter_value == 'DOY' %} ${created_day_of_year}
{% elsif choose_breakdown._parameter_value == 'DOM' %} ${created_day_of_month}
{% elsif choose_breakdown._parameter_value == 'DOW' %} ${created_day_of_week_index}
{% elsif choose_breakdown._parameter_value == 'Date' %} ${created_date}
{% else %}NULL{% endif %} ;;
}
dimension: sort_by2 {
hidden: yes
type: string
sql:
{% if choose_comparison._parameter_value == 'Year' %} ${created_year}
{% elsif choose_comparison._parameter_value == 'Month' %} ${created_month_num}
{% elsif choose_comparison._parameter_value == 'Week' %} ${created_week}
{% else %}NULL{% endif %} ;;
}
}
# ---------- EXPLORE ---------- #
explore: pop_simple {
label: "PoP Method 2: Allow users to choose periods with parameters"
always_filter: {
filters: [choose_comparison, choose_breakdown]
}