Period-Over-Period (POP) Analysis for Dashboard Filters

  • 12 December 2019
  • 1 reply
  • 1004 views

Userlevel 1

You may want to power up your dashboards with filters that allow users to choose periods for analysis. This implementation only has one filter, so it is easy to use and hard to make an invalid choice.




TLDR; (Too Long, Didn't Read)


  • A parameterized filter with a couple Period Over Period (POP) selections.

  • A set of yes/no dimensions for membership in each period.

  • POP measures filtered on the yes/no dimensions.

  • A sql always where on a yes/no dimension to limit results





How is this different from other Period Over Period analysis?

There are several other great implementations of POP analysis. My favorites are:


Fabio’s: https://help.looker.com/hc/en-us/articles/360023799293--Analytic-Block-Flexible-Period-over-Period-Analysis


Quinn’s: https://help.looker.com/hc/en-us/articles/360001285847-Timeframe-vs-Timeframe-Analysis-Using-Templated-Filters


Other implementations requires several filters to be used intelligently together; they are not intended for use as dashboard filters.



There are some other cool features of this method:



  • No SQL or join modification necessary; no chance for fan-out.

  • Dynamic label names are neat

  • Dynamic ‘html’ presentation of metrics is easily parameterized

  • POP changes (% and value differences) look good for many visualizations.

  • It’s fairly ‘DRY’ (Don’t Repeat Yourself)


Implementation


First, break up the target view into three views:



  • A ‘base view’ with only dimensions.

  • An ‘original view’ with the original view name and all the measures. Extends the base view.

  • A new ‘pop view’ for POP metrics. Empty at first, but extends the base view.


The rest of the code goes in the ‘POP view’, except for the new explore definition in the model file.


Create a parameterized measure with pre-defined periods. For this example, I’m using just two periods:



parameter: comparison {
hidden: yes
label: "Comparison"
description: "Type of period comparison."
type: unquoted
allowed_value: {
label: "Today vs Yesterday (to the same time)"
value: "dod_yt"
}
allowed_value: {
label: "Week to Date vs Last Week to Date"
value: "wow_wtd"
}
}


You can also parameterize the name of the comparison periods, so later measures can display dynamic period names:




Dynamic period names


dimension: comparison_period_name  {
hidden: yes
type: string
sql: {% if comparison._parameter_value == 'dod_yt' %} 'Yesterday'
{% elsif comparison._parameter_value == 'wow_wtd' %} 'Last Week'
{% else %} 'Comparison Period' {% endif %} ;;
}



Create some helper date dimension groups and a series of yes/no dimensions that evaluate to ‘Yes’ for records in the periods. In this example, I’m using a date from a time dimension group called ‘order’ in the base view, and redshift date functions.




Helper date fields


dimension_group: current {
type: time
hidden: yes
timeframes: [ date, day_of_week_index, week, time_of_day, time, raw, month_num, day_of_month ]
sql: now();;
}
dimension_group: yesterday {
type: time
hidden: yes
timeframes: [date, day_of_week_index, day_of_month, day_of_year, month_num, year]
sql: DATEADD(day,-1,${current_raw}) ;;
}
dimension_group: last_week {
type: time
hidden: yes
timeframes: [date, time_of_day, week, day_of_week_index, day_of_month, day_of_year, month_num, year]
sql: DATEADD(day,-7,${current_raw}) ;;
}





Yes/No Dimensions


dimension: is_in_first_period {
type: yesno
sql: {% if comparison._parameter_value == 'dod_yt' %}
${order_date} = ${current_date}
{% elsif comparison._parameter_value == 'wow_wtd' %}
${order_week} = ${current_week}
{% else %}
true
{% endif %}
;;
hidden: yes
}

dimension: is_in_comparison_period {
type: yesno
sql: {% if comparison._parameter_value == 'dod_yt' %}
${order_date} = ${yesterday_date}
AND ${order_time_of_day} <= ${current_time_of_day}
{% elsif comparison._parameter_value == 'wow_wtd' %}
${order_week} = ${last_week_week}
AND ${order_day_of_week_index} <= ${current_day_of_week_index}
AND ${order_time_of_day} <= ${current_time_of_day}
{% else %}
true
{% endif %}
;;
hidden: yes
}



To make the query efficient, add a dimension to use in the where clause:




filter dimension


dimension: is_in_either_period {
type: yesno
sql: ${is_in_first_period} OR ${is_in_comparison_period} ;;
hidden: yes
}



Create one or more measures. If you want to have the delta and % difference, up to four measures will be necessary for each metric. In this example, I’ll count the sales orders.




Primary period measure with dynamic label


measure: total_orders_this {
type: count_distinct
label: "{% if comparison._parameter_value == 'dod_yt' %} Order Count Today
{% elsif comparison._parameter_value == 'dod_y2' %} Order Count Yesterday
{% elsif comparison._parameter_value == 'dod_lw' %} Order Count Yesterday
{% elsif comparison._parameter_value == 'wow_wtd' %} Order Count Week To Date
{% elsif comparison._parameter_value == 'mom_mtd' %} Order Count Month to Date
{% else %} Total Orders This {% endif %}"
group_label: "Pd over Pd"
value_format_name: decimal_0
sql: ${sale_order_id} ;;
filters: {
field: is_in_first_period
value: "Yes"
}
html: <strong>{{rendered_value}}</strong><br/>
<em>{{ sale_order.total_order_percent._rendered_value }} from {{ sale_order.total_orders_last._rendered_value }} {{ comparison_period_name._value }}</em>;;
}





Comparison period, delta and percent measures


measure: total_orders_last {
type: count_distinct
group_label: "Pd over Pd"
value_format_name: decimal_0
sql: ${sale_order_id} ;;
filters: {
field: is_in_comparison_period
value: "Yes"
}
hidden: yes
}
measure: total_order_delta {
type: number
group_label: "Pd over Pd"
value_format_name: decimal_0
sql: ${total_orders_this} - ${total_orders_last} ;;
hidden: yes
}
measure: total_order_percent {
type: number
group_label: "Pd over Pd"
value_format_name: percent_2
sql: 1.0 * (${total_orders_this} - ${total_orders_last}) / NULLIF(${total_orders_last}, 0) ;;
hidden: yes
}



The Explore Definition

Add this new POP view to an explore. Add supporting joins, if necessary. Add an sql_always_where for efficiency, and an always_filter to include the parameter view:




SQL always where


sql_always_where: ${sale_order.is_in_either_period} ;;
always_filter: {
filters:{
field: sale_order.comparison
}
}



I’ll be posting a block soon with a larger example and further annotations. Please let me know if you have any questions!


Big thanks to @JeffH for introducing me to this method.


1 reply

Userlevel 7
Badge +1

Love the TLDR dropdown 😆



This is simple and clever — Well done!

Reply