Looker will not be updating this content, nor guarantees that everything is up-to-date.
This post describes the period over period (pop) Method 6: Any Two Arbitrary Periods 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]
}
}
Sometimes an analysis requires a comparison between two arbitrary periods, not including the current period. This is relevant for use cases such as web analytics, where, for example, the number of registrations for an upcoming webinar needs to be compared to a previous webinar from six months ago. The challenge is that the date ranges might have a different number of days, so the periods need to be aligned based on their start day.
Below is an example of a period-over-period analysis with custom periods:
Below is an example of a filtered measure based on custom periods made possible by this method:
Advantage:
Disadvantages:
'First Period'
and 'Second Period'
labels are not null.filter
field for the first date range. See first_period_filter
under USER FILTERS in the LookML example below.second_period_filter
under USER FILTERS in the LookML example below.days_from_start_first
and days_from_start_second
dimensions under HIDDEN HELPER DIMENSIONS in the LookML example below.days_from_first_period
dimension under DIMENSIONS TO PLOT in the LookML example below.'First Period'
or 'Second Period'
for the Explore analysis pivot. See the period_selected
dimension under DIMENSIONS TO PLOT in the LookML example below. Labeling won't work if your periods overlap. There are no joins, so each row can only be in the current or the previous period.
always_filter
parameter to the explore
, set to filter the pivot field with the condition -NULL
. This ensures that there will be no results outside the custom date ranges specified.Below is an example view file with the fields referenced above:
This example view 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 6: Compare two arbitrary date ranges
# Like Method 5, but allowing arbitrary definition of the 'current' period
# provides functionality like Google Analytics, which allows you to compare two arbitrary date ranges
include: "method1.view.lkml"
view: pop_arbitrary {
extends: [order_items]
## ------------------ USER FILTERS ------------------ ##
filter: first_period_filter {
view_label: "_PoP"
group_label: "Arbitrary Period Comparisons"
description: "Choose the first date range to compare against. This must be before the second period"
type: date
}
filter: second_period_filter {
view_label: "_PoP"
group_label: "Arbitrary Period Comparisons"
description: "Choose the second date range to compare to. This must be after the first period"
type: date
}
## ------------------ HIDDEN HELPER DIMENSIONS ------------------ ##
dimension: days_from_start_first {
view_label: "_PoP"
hidden: yes
type: number
sql: DATEDIFF('day', {% date_start first_period_filter %}, ${created_date}) ;;
}
dimension: days_from_start_second {
view_label: "_PoP"
hidden: yes
type: number
sql: DATEDIFF('day', {% date_start second_period_filter %}, ${created_date}) ;;
}
## ------------------ DIMENSIONS TO PLOT ------------------ ##
dimension: days_from_first_period {
view_label: "_PoP"
description: "Select for Grouping (Rows)"
group_label: "Arbitrary Period Comparisons"
type: number
sql:
CASE
WHEN ${days_from_start_second} >= 0
THEN ${days_from_start_second}
WHEN ${days_from_start_first} >= 0
THEN ${days_from_start_first}
END;;
}
dimension: period_selected {
view_label: "_PoP"
group_label: "Arbitrary Period Comparisons"
label: "First or second period"
description: "Select for Comparison (Pivot)"
type: string
sql:
CASE
WHEN {% condition first_period_filter %}${created_raw} {% endcondition %}
THEN 'First Period'
WHEN {% condition second_period_filter %}${created_raw} {% endcondition %}
THEN 'Second Period'
END ;;
}
## Filtered measures
measure: current_period_sales {
view_label: "_PoP"
type: sum
sql: ${sale_price};;
filters: [period_selected: "Second Period"]
}
measure: previous_period_sales {
view_label: "_PoP"
type: sum
sql: ${sale_price};;
filters: [period_selected: "First Period"]
}
measure: sales_pop_change {
view_label: "_PoP"
label: "Total sales period-over-period % change"
type: number
sql: (1.0 * ${current_period_sales} / NULLIF(${previous_period_sales} ,0)) - 1 ;;
value_format_name: percent_2
}
dimension_group: created {hidden: yes}
dimension: ytd_only {hidden:yes}
dimension: mtd_only {hidden:yes}
dimension: wtd_only {hidden:yes}
}
# ---------- EXPLORE ---------- ##
explore: pop_arbitrary {
label: "PoP Method 6: Compare two arbitrary date ranges"
always_filter: {
filters: [first_period_filter: "NOT NULL", second_period_filter: "NOT NULL", period_selected:"-NULL"]
}
}
Hi there!
For me, the code doesn’t work. I think it has something to do with the fact that my input for the date range filters is changed into a timestamp rather than a date (since the error message that I receive is that you can’t compare a date to a timestamp when applying the “<=” or “=” operator). I tried solving it with a format_date function, but unfortunately without any luck. Someone who can help me?
Thanks 🙂