Methods for Period Over Period (PoP) Analysis in Looker - Method 4: Current Period and Many Previous Periods


Userlevel 3

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

This post describes the period over period (pop) Method 4: Current Period and Many Previous 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]
}
}

 

Period-over-Period Method 4: Current Period and Many Previous Periods - Compare Multiple Templated Periods

 

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 4.

Sometimes you may want to compare more than just the current and previous periods. By adding a new parameter to choose the number of periods to compare, we can allow this; however, it does add a lot of verbosity to the model.

Below is an example looking at the current and previous three periods:

Advantages and Disadvantages

Advantages:

  • Performs the same function as Method 3: Custom Choice of Current and Previous Periods with Parameters.
  • Can accommodate more than two period comparisons in a PoP analysis.

Disadvantages:

  • Requires complex LookML modeling, including Liquid implementation.
  • The developer must decide how many periods to create parameter values for, which can potentially limit user analysis.
  • Overlapping periods are not supported (this requires a join).
  • Only a single date field may be used (e.g., the same functionality for other dates requires replicating all the code, per date).

Method

  1. Create a parameter with allowed values to choose the number of periods to compare. See the comparison_periods parameter in the LookML example below.
  2. Create two hidden dimensions to calculate the periods for each of the allowed_value subparameters — one for the start of each period, and one for the end of each period. See the period_3_start, period_3_end, period_4_start, and period_4_end dimensions below.
    Period two is defined in the extended view. See period_2_start and period_2_end under HIDDEN HELPER DIMENSIONS in the Method 3 LookML example.
  3. Adjust the period dimension in the LookML example below according to how many periods are available to the user for comparison. This field displays the correct label, depending on the number of periods selected by the user.
  4. Create a hidden dimension to order periods correctly by numerical value (e.g., period 1, period 2, period 3). See the order_for_period dimension in the LookML example below.
  5. Apply the parameter (comparison_periods in the LookML example below) in a sql_always_where in the Explore. See the pop_parameters_multi_period `explore` in the LookML example below.
  6. (Optional) Similar to Method 2, create additional (hidden) dimensions to correctly sort strings, such as month and day of week names, by their numerical or index counterparts. See day_in_period dimension in the LookML example below.
  7. (Optional) New filtered measures can be created for each possible previous period. See the examples under TO CREATE FILTERED MEASURES in the Method 3 LookML example.

LookML Example

Below is an example view file with the fields referenced above:

This example view extends the pop_parameters view referenced in Method 3, which extends the order_items view referenced in the introduction and in the implementation for Method 1. If you are not using Redshift, be sure to adapt any SQL in the sql parameters to your database dialect.
    ###  Period over Period Method 4: Compare multiple templated periods

# Like Method 3, but expanded to compare more than two periods

include: "method3.view.lkml"
# This extended version allows you to choose multiple periods (this can also work in conjunction with the custom range version, or separately)

view: pop_parameters_multi_period {
extends: [pop_parameters]

parameter: comparison_periods {
view_label: "_PoP"
label: "3. Number of Periods"
description: "Choose the number of periods you would like to compare."
type: unquoted
allowed_value: {
label: "2"
value: "2"
}
allowed_value: {
label: "3"
value: "3"
}
allowed_value: {
label: "4"
value: "4"
}
default_value: "2"
}

dimension: period_3_start {
view_label: "_PoP"
description: "Calculates the start of 2 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -(2 * ${days_in_period}), DATE({% date_start current_date_range %}))
{% else %}
DATEADD({% parameter compare_to %}, -2, DATE({% date_start current_date_range %}))
{% endif %};;
hidden: yes

}

dimension: period_3_end {
view_label: "_PoP"
description: "Calculates the end of 2 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -1, ${period_2_start})
{% else %}
DATEADD({% parameter compare_to %}, -2, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
{% endif %};;
hidden: yes
}

dimension: period_4_start {
view_label: "_PoP"
description: "Calculates the start of 4 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -(3 * ${days_in_period}), DATE({% date_start current_date_range %}))
{% else %}
DATEADD({% parameter compare_to %}, -3, DATE({% date_start current_date_range %}))
{% endif %};;
hidden: yes
}

dimension: period_4_end {
view_label: "_PoP"
description: "Calculates the end of 4 periods ago"
type: date
sql:
{% if compare_to._parameter_value == "Period" %}
DATEADD(DAY, -1, ${period_2_start})
{% else %}
DATEADD({% parameter compare_to %}, -3, DATEADD(DAY, -1, DATE({% date_end current_date_range %})))
{% endif %};;
hidden: yes
}

dimension: period {
view_label: "_PoP"
label: "Period"
description: "Pivot me! Returns the period the metric covers, i.e. either the 'This Period', 'Previous Period' or '3 Periods Ago'"
type: string
order_by_field: order_for_period
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
THEN 'This {% parameter compare_to %}'
WHEN ${created_date} between ${period_2_start} and ${period_2_end}
THEN 'Last {% parameter compare_to %}'
WHEN ${created_date} between ${period_3_start} and ${period_3_end}
THEN '2 {% parameter compare_to %}s Ago'
WHEN ${created_date} between ${period_4_start} and ${period_4_end}
THEN '3 {% parameter compare_to %}s Ago'
END
{% else %}
NULL
{% endif %}
;;
}

dimension: order_for_period {
hidden: yes
view_label: "Comparison Fields"
label: "Period"
type: string
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
THEN 1
WHEN ${created_date} between ${period_2_start} and ${period_2_end}
THEN 2
WHEN ${created_date} between ${period_3_start} and ${period_3_end}
THEN 3
WHEN ${created_date} between ${period_4_start} and ${period_4_end}
THEN 4
END
{% else %}
NULL
{% endif %}
;;
}
dimension: day_in_period {
description: "Gives the number of days since the start of each periods. Use this to align the event dates onto the same axis, the axes will read 1,2,3, etc."
type: number
sql:
{% if current_date_range._is_filtered %}
CASE
WHEN {% condition current_date_range %} ${created_raw} {% endcondition %}
THEN DATEDIFF(DAY, DATE({% date_start current_date_range %}), ${created_date}) + 1

WHEN ${created_date} between ${period_2_start} and ${period_2_end}
THEN DATEDIFF(DAY, ${period_2_start}, ${created_date}) + 1

WHEN ${created_date} between ${period_3_start} and ${period_3_end}
THEN DATEDIFF(DAY, ${period_3_start}, ${created_date}) + 1

WHEN ${created_date} between ${period_4_start} and ${period_4_end}
THEN DATEDIFF(DAY, ${period_4_start}, ${created_date}) + 1
END

{% else %} NULL
{% endif %}
;;
hidden: yes
}
}

# ---------- EXPLORE ---------- #

explore: pop_parameters_multi_period {
label: "PoP Method 4: Compare multiple templated periods"
extends: [pop_parameters]
sql_always_where:
{% if pop_parameters_multi_period.current_date_range._is_filtered %} {% condition pop_parameters_multi_period.current_date_range %} ${created_raw} {% endcondition %}
{% if pop_parameters_multi_period.previous_date_range._is_filtered or pop_parameters_multi_period.compare_to._in_query %}
{% if pop_parameters_multi_period.comparison_periods._parameter_value == "2" %}
or ${created_raw} between ${period_2_start} and ${period_2_end}
{% elsif pop_parameters_multi_period.comparison_periods._parameter_value == "3" %}
or ${created_raw} between ${period_2_start} and ${period_2_end}
or ${created_raw} between ${period_3_start} and ${period_3_end}
{% elsif pop_parameters_multi_period.comparison_periods._parameter_value == "4" %}
or ${created_raw} between ${period_2_start} and ${period_2_end}
or ${created_raw} between ${period_3_start} and ${period_3_end} or ${created_raw} between ${period_4_start} and ${period_4_end}
{% else %} 1 = 1
{% endif %}
{% endif %}
{% else %} 1 = 1
{% endif %};;
}

0 replies

Be the first to reply!

Reply