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

  • 12 December 2019
  • 1 reply
  • 1775 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