Looker will not be updating this content, nor guarantees that everything is up-to-date.
This post describes the period over period (pop) Method 5: Current Period and Any Arbitrary 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 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 5.
Sometimes you don't want to compare the current period to exactly the previous period. Adding another filter to Method 3 allows users to compare the current period with any other arbitrary date range.
Below is an example looking at the current period against a custom previous date range:
Advantages:
Disadvantages:
filter
field for the user to select the previous date range they want to compare against. See the previous_date_range
filter
field in the LookML example below.period_2_start
and period_2_end
dimensions 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. If you are not using Redshift, be sure to adapt any SQL in thesql
parameters to your database dialect.
### Period over Period Method 5: Compare current period with another arbitrary period
# Like Method 3, but allows you to compare the current period with any other arbitrary date range period
include: "method3.view.lkml"
## This extended version allows the user to also choose a custom date range for the previous period
view: pop_parameters_with_custom_range {
extends: [pop_parameters]
# custom date range
filter: previous_date_range {
type: date
view_label: "_PoP"
label: "2a. Previous Date Range (Custom):"
description: "Select a custom previous period you would like to compare to. Must be used with Current Date Range filter."
}
parameter: compare_to {label: "2b. Compare To:"}
dimension_group: date_in_period {hidden:yes}
dimension: period_2_start {
view_label: "_PoP"
description: "Calculates the start of the previous period"
type: date
sql:
{% if compare_to._in_query %}
{% 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 %}
{% else %}
{% date_start previous_date_range %}
{% endif %};;
hidden: yes
}
dimension: period_2_end {
hidden: yes
view_label: "_PoP"
description: "Calculates the end of the previous period"
type: date
sql:
{% if compare_to._in_query %}
{% 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 %}
{% else %}
{% date_end previous_date_range %}
{% endif %};;
}
}
# ---------- EXPLORE ---------- #
explore: pop_parameters_with_custom_range {
label: "PoP Method 5: Compare current period with another arbitrary period"
always_filter: {
filters: [current_date_range: "1 month", previous_date_range: "2 months ago for 2 days"]
}
}