[Analytic Block] Flexible Period-over-Period Analysis

Looker Q&A Looker
,
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
  }
}
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.

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)

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.

25

Is the code in GitHub?

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!

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

27%20PM.png
27%20PM.png2880×1272 262 KB

05%20PM.png
05%20PM.png2880×1264 305 KB

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

56%20PM.png
56%20PM.png2880×1366 292 KB

49%20PM.png
49%20PM.png2880×1258 305 KB

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!

28

Hey guys,

I’m trying to implement this code in our MS SQL Server environment and I get the error
“always_join: Unknown Join pop”

Not sure what causes this and how I can fix it…

29

Razz:

I’m trying to implement this code in our MS SQL Server environment and I get the error> “always_join: Unknown Join pop”

That error says to me that there’s something iffy with the way you’ve defined your views. In the code in the top level post here, at the very end, there’s a view defined called pop that’s joined into some explores:

view: pop {
        sql_table_name: (SELECT NULL) ;;
        view_label: "[PoP]"
etc etc

Is that also present in your LookML? If you’ve renamed it, then that would also break the always_join reference to it.

30

Hey Miguel, I like your solution but could you please elaborate on your example model with the weather_raw measure and weather_date dimension? I tried to create the weather_raw as an average temperature and create a weather_date dimension but I received an error “Field references an aggregate but is specified as a “dimension”. If you want to use aggregations such as sum, average, count, use a measure type instead.”

31

@fabio1 : What does this piece of code actually do? I’m used to using SQL_TABLE_NAME as a reference to a derived table as in ${some_pdt.SQL_TABLE_NAME}

dimension: SQL_TABLE_NAME { sql: order_items;; hidden:yes}

Thanks,

32

That declaration, and the two that follow it are 100% informational for the person implementing the code as to how to do the substitutions in the following code block.

image.png
image.png639×251 33.3 KB

I named it like the existing SQL_TABLE_NAME gesture because it is doing the same thing, providing the name of the table that should be referenced.

Final note, this was necessary back in the day to work around the fact that Looker would not do substitutions inside of the view>sql_table_name parameter. There are surely cleaner ways of implementing this now, but anyway I usually suggest to people not to use this pattern anymore and instead to use on pivots + built-in datepart dimensions, like day_of_month (along with a solution like “outer join on false” or “join paths” if they need to combine datasets without fanout)

33

@fabio1 Would you be willing to post the “cleaner” way to implementing this code now? I would greatly appreciate it.

34

I mostly recommend customers away from this approach nowadays and instead suggest using Looker’s default dateparts together with pivoting for a better user experience, and then using something like my join paths approach if there is a need to combine multiple fact tables. This allows for less manually written SQL and better drill downs.

If you are set on using the “PoP” approach in this article, the thing I was alluding to before is that I believe you should now be able to use, for example, ${order_items.SQL_TABLE_NAME} inside of the view>sql_table_name parameter

35

Hi fabio - thanks very much for this. I’m wondered if you could expand on your statement, “… suggest using Looker’s default dateparts together with pivoting…” or point me to the documentation that discusses this as a way to do PoP analysis, please?

36

For example, you can select “month of year”/“monthname” as a dimension and “year” as a pivot.

When doing this, I find it helps to put the two classes of date parts (i.e., period, and within-period) into two separate view labels.

It can also help to create YTD, MTC, etc filters. Here are a couple examples of that:

[Month to Date and Year to Date Analysis](https://discourse.looker.com/t/month-to-date-and-year-to-date-analysis/168) Analytical Patterns & Blocks

Month-to-Date (MTD) and Year-to-Date (YTD) analyses are useful when conducting timeframe-based comparisons. For example, while you are part of the way through a month, you may want to compare your progress with earlier months. However, it would not be useful to compare part of the current month with the entirety of earlier months. Instead, it would make more sense to only look at the days in earlier months that have already transpired in the current month. The best way to execute this type o…

37

Hi, I m trying to adapt this for PostgreSQL but i m stucked !!!
It would be great if you post the equivalent code if exists
Thanks

38

I have used the same logic mentioned above for redshift dialect here and I am seeing 7 months when I choose (is in the past :6 months) This also works the same when I select complete months - I am able to see May 2021 when I choose complete months. Same is the case for complete quarters as well. Anyone face the same issue here?

39

Thanks for sharing @fabio1 . Can you help me with below scenario?

Need help in getting past 4 quarters of data based on the filter selection on dashboard.

In the source table, we have data at quarter level ( have Year and Quarter column with other measures)

Example:
Year Quarter Orders
2019 1 100
2019 2 200
2019 3 50
2019 4 90
2020 1 300

On the dashboard, have a filter in which user will select the Quarter ( this filter will have values like 2019Q1,2019Q2 so on which utilizes a dimension built using Year and Quarter)
Once the user selects the Quarter, then the visualization should only show past 4 quarters of data.

New to looker, any help here would be appreciated.

40

Can’t access the github link for BQ that @MiguelGarcia1 posted.

Does anyone have the updated github link for Big Query?