[Analytic Block] Flexible Period-over-Period Analysis

visualizations
lookml

(Mark Goodwin) #21

hi @sami.rubenfeld, here is the code, let me know if this helps:

view: pop {
  sql_table_name: (SELECT NULL) ;;
  view_label: "[PoP]"
  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)
      ;;}
  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_end pop.date_filter %} - INTERVAL "'"${within_periods.n} {% parameter pop.within_period_type %}"'");;
  }
  filter: date_filter  {
    label: "1. Date Range"
    hidden: yes
    type: date
    convert_tz: no
  }
  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"
      }
    }

  }
  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"
      }
    }
  }
  filter: over_how_many_past_periods {
    label: "Override past periods"
    description: "Apply this filter to change which past periods to compare to (from the default of current vs 1 period ago)"
    type: number
    default_value: "<=1"
  }
  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
  }
}

(sami.rubenfeld) #22

Hi Mark,

The correct syntax for Postgres would be the following:

view: test_interval {

  dimension: current_date {
    type: date
    sql: NOW() ;;
  }

  parameter: date_type {
    type:  string
  }

  parameter: period_length {
    type: number
  }

  dimension: both {
    type: date
    sql: ${current_date} +  ({% parameter period_length %} || {% parameter date_type %}) :: interval ;;
  }
}

In this example, in the date_type filter you would input the time frame and in the period_length filter you would input a number. To get 5 days into the future, you would put day and 5 in the respective filters.


(Joseph Song) #23

Any idea on how one would adapt this block to compare YoY, where last year = this year - 364 days?
(to match Mondays to Mondays)


(fabio) #24

Hi Joseph, I think you can just use the block as-is, and choose ā€œcompare overā€ = week, and bring in the optional ā€œpast periodsā€ parameter and set it to 52, or if you want to compare multiple weeks, 52,53,54, etc. Take a look at the examples in the post, one of them shows this.


(Sonny) #25

Is the code in GitHub?


(srinija.godavarthi) #26

Hey @sonnypolaris , the code for the year over year reporting is in Fabioā€™s original post (in Redshift). If you would like the MySQL version, itā€™s in Gordenā€™s reply in the comments!


(Miguel Garcia) #27

I used the period over period structure above and combined it with the join on false approach here to do something similar on Google BigQuery.

Goal: Compare any two arbitrary periods from the same table with varying levels of granularity (e.g. broken up by day, month, quarter, year, etc.)

Examples below are based on weather data but the approach can be adapted to any table that has date and a measure of interest.

Example 1: Comparing two months (July, 2018 and March, 2018) at the day level

Example 2: Comparing two quarters (Q1, 2018 and Q3, 2018) at the month level

LookML Highlights:

  1. sql_on: FALSE - when using an outer join it accomplishes the equivalent of a wide union, think of a diagonal table with lots of nulls
  2. extracting info from Looker-generated predicate after it translates liquid condition - allows us to determine when both of the arbitrary periods start
  3. using a parameter to label a dimension - shows Week/Day/etc. in the viz based on userā€™s input

Gist to LookML

Hope others find it useful!