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 8: Arbitrary Period and Many Previous Periods highlighted in the main Methods for Period Over Period (PoP) Analysis in Looker Community post.
Period-over-Period Method 8: Arbitrary Period and Many Previous Periods - Compare Any Period to Any Other Period
This method allows users to choose the current period, the grouping for that period, which period to compare (pivot), and how many periods to compare. This is the most complex PoP method, but it is also the most flexible.
Below is an example with order items in the current month-to-date, broken down by day and compared to last month:
Below is an example with an arbitrary date range, broken down by day and compared to the same date range for the previous three years:
Advantages and Disadvantages
Advantages:
- The date range can be broken to a more granular level (e.g., months, days, weeks).
- You can be very specific about which prior periods to compare, and you can quickly compare multiple periods at once (e.g., "x periods ago" or "past y periods").
- This method defines possible previous periods in bulk. This is in contrast to Method 4, where previous periods are defined manually and individually.
- The joins in this method allow for overlapping period comparison.
- The LookML for this method lives in its own
explore, so it is kept separate from any complexity in the rest of the model. - Although the initial setup is time consuming, the approach is modular and relatively easy to scale to add new date dimensions. You will only need a single new derived table and join for each date dimension. To achieve this, make a new view with a derived table for each new date, add the desired measures to the derived table definition, and join the new view to the
explore. See thepop_order_items_createdandpop_order_items_deliveredviews in the LookML example below.- The structure is similar to joining different views to a central date dimension table. This makes it quick to compare different aggregations for the same period without triggering symmetric aggregates, which can limit expensive queries.
Disadvantages:
- This method requires its own
explore, whereas other approaches can be incorporated into a single view file.- Any complex data transformations or joins required for the
exploreneed to be replicated for each new date added, as the model is scaled. - Users must navigate to a new Explore solely for PoP analysis.
- Any complex data transformations or joins required for the
- Measures need to be defined in advance, rather than existing measures being reused. Any existing logic would need to be replicated, as the model is scaled to incorporate new dates.
- The LookML is complex; maintenance and collaboration may be difficult.
Method
- Create two views: one to configure the filters and plotted date dimensions, and a second to establish the maximum number of previous periods for comparison. These two views will remain untouched, as the method is scaled to include additional dates. See the
popandnumbersviews in the LookML below. - In the first view, create four hidden fields that become the filters users interact with, as depicted in the examples above. Two are
filterfields, and two are dimensions:These filters are hidden to avoid cluttering the Explore field picker. The filters are surfaced in the Explore with an
always_filterparameter. See theflexible_pop exploreunder EXPLORE in the LookML example below.- Filter 1 — Date Range: A filter of
type: datefor users to select the date range on which to filter. See thedate_filterfield under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below. - Past Periods Filter: A filter of
type: numberfor users to choose the number of past periods to compare. If the user makes no selection, the number of past periods will default to 1. See theover_how_many_past_periodsfield under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below. - Filter 2 — Date Range Breakdown: A dimension of
type: stringfor users to select the granularity of the selected date range (in quarters, months, weeks, days) by which to group aggregates. This dimension uses LookMLcaseparameters to control the values the user can choose from. See thewithin_period_typefield under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below. - Filter 3 — Previous Period: A second dimension of
type: stringfor users to select the time period for comparison and the granularity by which to group aggregates. Similar to Filter 2, this dimension uses LookMLcaseparameters to control the values the user can choose from. See theover_period_typefield under (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH in the LookML example below.
- Filter 1 — Date Range: A filter of
- Next, in the second maximum periods view (
numbersbelow), create a hidden dimension oftype: numberto represent the number of periods. We will use this dimension in the next step. See thendimension in thenumbersview below. - Returning to the first view, create three dimensions:
- One hidden dimension to serve as an
order_byfield. This dimension calculates the interval for the dimension to be plotted as rows and ensures it will always be ordered correctly. See thereference_datedimension under DIMENSIONS WE WILL ACTUALLY PLOT in the LookML example below. - A second dimension to plot for rows, with appropriate date formatting applied. This field will be ordered by the hidden dimension above. See the
reference_date_formatteddimension under DIMENSIONS WE WILL ACTUALLY PLOT in the LookML example below. - The last dimension will be for pivoting the prior periods for comparison. It is ordered by the
type: numbernumber of periods dimension (n, in this example) from the second maximum periods view. This ensures that each previous period is ordered in the correct way. Seeover_periods_agounder DIMENSIONS WE WILL ACTUALLY PLOT in the LookML example below.
- One hidden dimension to serve as an
- Create an
explore, joining the first two views together. The maximum periods view,numberswill be joined twice — once for calculating the size of the selected current period, in the units selected by the Date Range Breakdown filter (within_period_type), and again to determine the number of previous periods, as selected by the Past Periods Filter (over_how_many_past_periods). See thewithin_periodsandover_periodsjoins in theflexible_pop exploreunder EXPLORE in the LookML example below. - Now, create measures, or aggregations, based on each date field for users to compare. For example, an orders created date or an orders delivered date. This requires developing one derived table view per date field.
- Create one view and one derived table per date field. The derived table
sqldefinition needs to include a date for joining the view to the originalexplore. This join date is the date field, which is updated dynamically to match the date granularity selected in Filter 2 — Date Range Breakdown from the first view. The derived tablesqldefinition also should contain the desired aggregations, such asCOUNTandSUM. See thepop_order_items_createdview under VIEWS TO EDIT in the LookML example below. - Define the aggregations as LookML measures that users can select and view in an Explore. See
agg_1andagg_2in thepop_order_items_createdview under VIEWS TO EDIT in the LookML example below. - Join the view into the
explore. See thepop_order_items_createdjoin in theflexible_popexplore under EXPLORE in the LookML example below. - Repeat the above steps for the additional date field aggregations.
- Create one view and one derived table per date field. The derived table
LookML 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. ### Period over Period Method 8: Compare any period to any other period
# the most complex implementation, but also the most flexible
#### ------------ VIEWS TO LEAVE ALONE ------------ ###
# This view sets up the config (doesn't need editing)
view: pop {
sql_table_name: (SELECT NULL) ;;
view_label: "_PoP"
### ------------ (HIDDEN) FILTERS TO CUSTOMIZE THE APPROACH ------------
# Choose a date range to filter on
filter: date_filter {
label: "1. Date Range"
hidden: yes
type: date
convert_tz: no
}
# A second filter to choose number of past periods. Defaults to 1 if this is not selected
filter: over_how_many_past_periods {
label: "Override past periods"
description: "Apply this filter to add past periods to compare to (from the default of current vs 1 period ago)"
type: number
default_value: "<=1"
}
# Choose how to break the range down - normally done with a parameter but here is a dimension
dimension: within_period_type {
label: "2. Break down date range by"
hidden: yes
type: string
#Using case just to get friendlier UI experience in filters. Otherwise, could have a no-sql filter field
case: {
when: {
sql: {% parameter pop.within_period_type %}='quarter' ;;
label: "quarter"
}
when: {
sql: {% parameter pop.within_period_type %}='month' ;;
label: "month"
}
when: {
sql: {% parameter pop.within_period_type %}='week' ;;
label: "week"
}
when: {
sql: {% parameter pop.within_period_type %}='day' ;;
label: "day"
}
when: {
sql: {% parameter pop.within_period_type %}='hour' ;;
label: "hour"
}
}
}
# Choose the previous period
# Again we use a dimension here instead of a parameter
dimension: over_period_type {
label: "3. Compare over"
hidden: yes
type: string
#Using case just to get friendlier UI experience in filters. Otherwise, could have a no-sql filter field
case: {
when: {
sql: {% parameter pop.over_period_type %}='year' ;;
label: "year"
}
when: {
sql: {% parameter pop.over_period_type %}='quarter' ;;
label: "quarter"
}
when: {
sql: {% parameter pop.over_period_type %}='month' ;;
label: "month"
}
when: {
sql: {% parameter pop.over_period_type %}='week' ;;
label: "week"
}
when: {
sql: {% parameter pop.over_period_type %}='day' ;;
label: "day"
}
}
}
### ------------ DIMENSIONS WE WILL ACTUALLY PLOT ------------
# This is the dimension we will plot as rows
# This version is always ordered correctly
dimension: reference_date {
hidden: yes
#type: date_time <-- too aggressive with choosing your string formatting for you
#type: date <-- too aggressive with truncating the time part
#convert_tz: no
#type: nothing <-- just right
sql: DATE_TRUNC({% parameter pop.within_period_type %},DATE_ADD({% parameter pop.within_period_type %},0 - ${within_periods.n} - 1,{% date_end pop.date_filter %}));;
}
# This is the version we will actually plot in the data with nice formatting
dimension: reference_date_formatted {
type: string
order_by_field: reference_date
label: "Reference date"
sql: TO_CHAR(
${reference_date},
CASE {% parameter pop.within_period_type %}
WHEN 'year' THEN 'YYYY'
WHEN 'month' THEN 'MON YY'
WHEN 'quarter' THEN 'YYYY"Q"Q'
WHEN 'week' THEN 'MM/DD/YY' --or 'YYYY"W"WW' or 'YY-MM"W"W'
WHEN 'day' THEN 'MM/DD/YY'
WHEN 'hour' THEN 'MM/DD HHam'
ELSE 'MM/DD/YY'
END)
;;}
# This is the dimension we will plot as pivots
dimension: over_periods_ago {
label: "Prior Periods"
description: "Pivot me!"
sql: CASE ${over_periods.n}
WHEN 0 THEN 'Current '||{% parameter pop.over_period_type %}
WHEN 1 THEN ${over_periods.n}||' '||{% parameter pop.over_period_type %} || ' prior'
ELSE ${over_periods.n}||' '||{% parameter pop.over_period_type %} || 's prior'
END;;
order_by_field: over_periods.n
}
}
# This view establishes the max number of previous periods (doesn't need editing)
view: numbers {
sql_table_name: (
SELECT 00 as n UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL
SELECT 03 UNION ALL SELECT 04 UNION ALL SELECT 05 UNION ALL
SELECT 06 UNION ALL SELECT 07 UNION ALL SELECT 08 UNION ALL
SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL
SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL
SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL
SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL
SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL
SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL
SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL
SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL
SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL
SELECT 51 UNION ALL SELECT 52 )
;;
dimension: n {
type: number
hidden: yes
sql: ${TABLE}.n ;;
}
}
#### ------------ VIEWS TO EDIT ------------ ###
view: pop_order_items_created {
view_label: "Order Items (By created date)"
sql_table_name: (
SELECT
DATE_TRUNC({% parameter pop.within_period_type %}, order_items.created_at) as join_date,
COUNT(*) as agg_1,
SUM(order_items.sale_price) as agg_2
FROM order_items
-- Could add templated filters here
-- OPTIONAL : Filter inner query on min/max dates (since query optimizer probably won't)
GROUP BY 1
) ;;
measure: agg_1 {
type: number
label: "Count"
sql: SUM(${TABLE}.agg_1) ;;
}
measure: agg_2 {
type: number
label: "Total Sales"
sql: SUM(${TABLE}.agg_2) ;;
}
}
#### ------------ EXTENSIBLE VERSION (Multiple dates) ------------ ###
## This is what the above looks like if we scale it to work with multiple date fields
## We make one view per date and join them all to the model
view: pop_order_items_delivered {
view_label: "Order Items (By delivered)"
sql_table_name: (SELECT
DATE_TRUNC({% parameter pop.within_period_type %},order_items.shipped_at) as join_date,
COUNT(*) as agg_1,
SUM(order_items.sale_price) as agg_2
FROM order_items
WHERE {%condition pop_order_items_delivered.sale_price %}order_items.sale_price{% endcondition %}
GROUP BY 1
) ;;
}
### ------------ EXPLORE ----------------###
explore: flexible_pop {
label: "PoP Method 8: Flexible implementation to compare any period to any other"
from: pop
view_name: pop
# No editing needed - make sure we always join and set up always filter on the hidden config dimensions
always_join: [within_periods,over_periods]
always_filter: {
filters: [pop.date_filter: "last 12 weeks", pop.within_period_type: "week", pop.over_period_type: "year"]
}
# No editing needed
join: within_periods {
from: numbers
type: left_outer
relationship: one_to_many
fields: []
# This join creates fanout, creating one additional row per required period
# Here we calculate the size of the current period, in the units selected by the filter
# The DATEDIFF unit is in days, so if we want hours we have to multiply it by 24
# (It might be possible to make this more efficient with a more granular function like TIMESTAMPDIFF where you can specify the interval units)
sql_on: ${within_periods.n}
<= (DATEDIFF( {% parameter pop.within_period_type %},{% date_start pop.date_filter %},{% date_end pop.date_filter %} ) - 1 )
* CASE WHEN {%parameter pop.within_period_type %} = 'hour' THEN 24 ELSE 1 END;;
}
# No editing needed
join: over_periods {
from: numbers
view_label: "_PoP"
type: left_outer
relationship: one_to_many
sql_on:
CASE WHEN {% condition pop.over_how_many_past_periods %} NULL {% endcondition %}
THEN
${over_periods.n} <= 1
ELSE
{% condition pop.over_how_many_past_periods %} ${over_periods.n} {% endcondition %}
END;;
}
# Rename (& optionally repeat) below join to match your pop view(s)
join: pop_order_items_created {
type: left_outer
relationship: many_to_one
#Apply join name below in sql_on
sql_on: pop_order_items_created.join_date = DATE_TRUNC({% parameter pop.within_period_type %},
DATEADD({% parameter pop.over_period_type %}, 0 - ${over_periods.n},
DATEADD({% parameter pop.within_period_type %}, 0 - ${within_periods.n},
{% date_end pop.date_filter %}
)
)
);;
}
}
