LinkedIn
Twitter
Looker will not be updating this content, nor guarantees that everything is up-to-date.
This post describes the period over period (pop) Method 7: Arbitrary Period and Directly 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]
}
} Period-over-Period Method 7: Arbitrary Period and Directly Previous Period - Compare Any Period with the Previous Preceding Period of the Same Length
This method enables users to select any date range and compare it to the previous period of the same length. For example, if a user selects a range of 12 days in the past, the data will be compared to 12 days directly preceding that range. This is particularly useful for single value visualization comparisons.
Below is an example of a single value visualization comparison between a custom period and the period of the same length directly preceding it:
Advantages and Disadvantages
Advantages:
- The previous period dynamically matches the interval of the selected date range.
- Limited Liquid is required in the implementation.
- Filtered measures are supported.
- The date range filter can be used on dashboards so that view-only users can manipulate the filter values.
When you are applying the filter to a dashboard: If there is another date filter on the dashboard, make sure that the tiles required to update with the PoP analysis listen to the PoP date filter.
Disadvantage:
- This method does not allow for gaps between periods; only directly preceding periods can be compared.
Method
- Create a date
filterfield for users to enter a date or date range for a Look or dashboard. See thedate_filterfield in the LookML example below. - Create two hidden fields of
type: timeto convert the start date and end date entered by the user into raw dates. Arawtimeframe will allow flexibility in the interval calculation in the following step. In order to account for the possibility that a user selects "is before" logic in the date filter, the start date and end date fields wrap the filter logic in a CASE statement that will make the start date "2013-01-01" (this date can be a Unix epoch, "1970-01-01", or you can use the first date in your database). The same CASE logic is used when we convert the end date of the filter to account for the user selecting "is on or after" (where the end date is the current date). See thefilter_start_dateandfilter_end_datedimensions in the LookML example below. - Create a hidden dimension to calculate the interval, or difference, between the filter start date and end date. See the
intervaldimension in the LookML example below. - Create a hidden dimension to calculate the start date of the previous period by subtracting the interval dimension from the start date. See the
previous_start_datedimension in the LookML example below. - Create a field to label the two time periods
"Selected Period"and"Previous Period"in the user-facing content. The previous period is the period between the previous start date and the start of the selected period (the previous period ends when the selected period begins). See thetimeframesdimension in the LookML example below.- The logic indicating whether a date is within the selected period or previous period is defined in two dimensions of
type: yesnothat are then referenced in thetimeframesdimension to label the period appropriately. The logic inyesnodimensions uses an existing date field,${created_date}, and can be reused to create filtered measures. Seeis_current_periodandis_previous_periodunder For Filtered Measures in the LookML example below. - Alternatively, code the logic from the
yesnofields directly into the CASE statement in thetimeframesdimension.
- The logic indicating whether a date is within the selected period or previous period is defined in two dimensions of
- (Optional) Create filtered measures. See
selected_period_order_revenueandprevious_period_order_revenueunder For Filtered Measures in the LookML example below.
LookML Example
Below is an example view file with the fields referenced above:
This example view extends theorder_itemsview referenced in the introduction. If you are not using Redshift, be sure to adapt any SQL in thesqlparameters to your database dialect.
### Period over Period Method 7: Compare any period with the previous preceding period of the same length
# Like Method 2, but here we define any date filter (not just current) and
# compare it to the previous period of the same length - great for single value visualization comparisons
include: "method1.view.lkml"
view: pop_previous {
extends: [order_items]
filter: date_filter {
view_label: "_PoP"
description: "Use this date filter in combination with the timeframes dimension for dynamic date filtering"
type: date
}
dimension_group: filter_start_date {
hidden: yes
type: time
timeframes: [raw,date]
sql: CASE WHEN {% date_start date_filter %} IS NULL THEN '2013-01-01' ELSE CAST({% date_start date_filter %} AS DATE) END;;
}
dimension_group: filter_end_date {
hidden: yes
type: time
timeframes: [raw,date]
sql: CASE WHEN {% date_end date_filter %} IS NULL THEN CURRENT_DATE ELSE CAST({% date_end date_filter %} AS DATE) END;;
}
dimension: interval {
hidden: yes
type: number
sql: datediff(day, ${filter_start_date_raw}, ${filter_end_date_raw});;
}
#start date of the previous period
dimension: previous_start_date {
hidden: yes
type: string
sql: DATEADD(day, - ${interval}, ${filter_start_date_raw});;
}
dimension: timeframes {
view_label: "_PoP"
type: string
case: {
when: {
sql: ${is_current_period} = true;;
label: "Selected Period"
}
when: {
sql: ${is_previous_period} = true;;
label: "Previous Period"
}
else: "Not in time period"
}
}
## For filtered measures
dimension: is_current_period {
hidden: yes
type: yesno
sql: ${created_date} >= ${filter_start_date_date} AND ${created_date} < ${filter_end_date_date} ;;
}
dimension: is_previous_period {
hidden: yes
type: yesno
sql: ${created_date} >= ${previous_start_date} AND ${created_date} < ${filter_start_date_date} ;;
}
measure: selected_period_order_revenue {
view_label: "_PoP"
type: sum
sql: ${sale_price} ;;
filters: [is_current_period: "yes"]
value_format_name: decimal_1
}
measure: previous_period_order_revenue {
view_label: "_PoP"
type: sum
sql: ${sale_price} ;;
filters: [is_previous_period: "yes"]
value_format_name: decimal_1
}
dimension: ytd_only {hidden:yes}
dimension: mtd_only {hidden:yes}
dimension: wtd_only {hidden:yes}
}
# ---------- EXPLORE ---------- #
explore: pop_previous {
label: "PoP Method 7: Compare any period with the previous period"
always_filter: {
filters: [date_filter]
}
sql_always_where: ${timeframes} <>'Not in time period' ;;
}