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 1: Any Two Native Timeframes highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.
This example uses anorder_items table from an e-commerce dataset. The example LookML view below is the basis of this example:
All SQL in the LookMLsqlparameters 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]
}
} PoP Method 1: Any Two Native Timeframes (Using Looker's Native Date Dimension Groups)
This is the simplest method for implementing PoP analysis in Looker. It uses two native date timeframes and a pivot. Alternatively, you can use a yesno field or custom filter for period-to-date comparison logic.
This method is easy to model; however, if you desire any level of user interactivity, your users will need explore permissions and the ability to navigate and assemble Explore analyses.
Using this method, users can perform analyses like the ones below.
Year-over-year (grouped by month, pivoted by year):
Month-over-month (grouped by day of month, pivoted by month):
Advantages and Disadvantages
Advantage:
- Users can self-serve PoP analyses in an Explore. Developing additional fields (unless you create a
yesnoLookML field on which to filter, as in the examples above) or Liquid implementation is not required.
Disadvantages:
- The type of comparison (e.g., year-over-year, month-over-month) cannot be changed without choosing different fields in an Explore.
- This can be time-consuming and potentially confusing for users to do themselves in an Explore and is impossible for users without
explorepermissions. - Importantly, this means it is impossible to control the PoP analysis using dashboard filters, as they can only be applied to a single field.
- This can be time-consuming and potentially confusing for users to do themselves in an Explore and is impossible for users without
- This method does not easily support filtered measures. For example, logic such as "% change vs previous period" must be achieved with a table calculation, rather than with a measure.
Method
- Choose the measure(s) to plot in an Explore.
- Choose one date
timeframeto group the measure by (e.g., day of month). See thecreateddimension_groupin the LookML example below. - Choose a second date dimension
timeframeto pivot by (e.g., month). - Apply a filter to the pivoted date dimension, and filter for the past two periods (e.g., past two months). See the month-over-month example above.
- (Step 4 Alternatives) You may also wish to implement period-to-date logic, such as month-to-date and year-to-date, to your PoP analysis. This can be achieved with
yesnoLookML fields with which to filter. See themtd_onlyandytd_onlydimensions in the LookML example below. Or, you can apply period-to-date logic with a custom Explore filter.
- (Step 4 Alternatives) You may also wish to implement period-to-date logic, such as month-to-date and year-to-date, to your PoP analysis. This can be achieved with
LookML Example
Below is an example view file with the fields referenced above:
The SQL in the sql parameters below is for Redshift. Be sure to adapt the example to your database dialect. ### Period over Period Method 1: Use Looker's native date dimension groups
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
}
#(Method 1a) you may also wish to create MTD and YTD filters in LookML
dimension: wtd_only {
group_label: "To-Date Filters"
label: "WTD"
view_label: "_PoP"
type: yesno
sql: (EXTRACT(DOW FROM ${created_raw}) < EXTRACT(DOW FROM GETDATE())
OR
(EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM GETDATE()) AND
EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM GETDATE()))
OR
(EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM GETDATE()) AND
EXTRACT(HOUR FROM ${created_raw}) <= EXTRACT(HOUR FROM GETDATE()) AND
EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM GETDATE()))) ;;
}
dimension: mtd_only {
group_label: "To-Date Filters"
label: "MTD"
view_label: "_PoP"
type: yesno
sql: (EXTRACT(DAY FROM ${created_raw}) < EXTRACT(DAY FROM GETDATE())
OR
(EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM GETDATE()) AND
EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM GETDATE()))
OR
(EXTRACT(DAY FROM ${created_raw}) = EXTRACT(DAY FROM GETDATE()) AND
EXTRACT(HOUR FROM ${created_raw}) <= EXTRACT(HOUR FROM GETDATE()) AND
EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM GETDATE()))) ;;
}
dimension: ytd_only {
group_label: "To-Date Filters"
label: "YTD"
view_label: "_PoP"
type: yesno
sql: (EXTRACT(DOY FROM ${created_raw}) < EXTRACT(DOY FROM GETDATE())
OR
(EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM GETDATE()) AND
EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM GETDATE()))
OR
(EXTRACT(DOY FROM ${created_raw}) = EXTRACT(DOY FROM GETDATE()) AND
EXTRACT(HOUR FROM ${created_raw}) <= EXTRACT(HOUR FROM GETDATE()) AND
EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM GETDATE()))) ;;
}
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]
}
}
# ---------- EXPLORE ----------- #
explore: order_items {
label: "PoP Method 1: Use Looker's native date dimension groups"
} See the How to do Period-over-Period Analysis Community post for another example of implementing this method using custom filters.