[Analytic Block] Flexible Period-over-Period Analysis

Looker has created this block to make analyzing data easier and more efficient. This Data Block is made available on an “as is” basis, meaning there will not be updates moving forward.

This is an advanced analytic block that assumes an advanced understanding of LookML, Liquid and SQL.

What Is This Block and What Does It Tell Me?

Year-over-year reporting (and, more generally, period-over-period analysis) can be complex. Data that isn’t joined properly can cause fanout and/or nested loop joins, both of which can potentially cause performance issues. The risk of fanout is often addressed by writing several variant SQL queries that group the underlying data by the right date granularity. This works, but it requires writing and maintaining several sets of nearly identical logic.

Fear not! With a bit of wizardry and by using parameters, we can create one Explore to rule them all!

The example LookML code for this solution is in the code block below; but first, let’s take a look at what the end result is and how it all fits together.

When a user first arrives at the resulting period-over-period Explore, they will see this:

By leveraging the always_filter Explore parameter, the user is always presented with the three filters below in which they must input values. However, the fields that make up these filters ([PoP] 1. Date Range, [PoP] 2. Break down date range by, [PoP] 3. Compare over) are hidden to avoid cluttering up the left-hand field picker. The first of these filters, [PoP] 1. Date Range, functions much like a typical date filter. However, it is also used to pick corresponding data in previous periods as well, by applying some date transformations to the start and end of the filter range in the back-end LookML. The other two filters leverage LookML case parameters to control the values the user can choose from. With these two filters, we are asking the user to pick the granularity they want for their date aggregates, along with the time period over which they want to compare the data.

That all sounds very abstract, so let’s list a few examples. A user might ask to see this data:

  • (1) The current month to date, (2) broken down by day, and (3) compared to the past month:

  • (1) The current year to date, (2) broken down by week, and (3) compared to last year:

  • (1) From Oct-1 to Mar-31, (2) broken down by month, and (3) compared to the prior year:

  • (1) The past 2 days, (2) broken down by hour, and (3) compared to the prior week:

  • A user can also change how many past periods to compare to, by bringing in an additional optional filter:

  • (1) The past 7 days, (2) broken down by day, and (3) compared to the 8 prior weeks:

  • (1) The past 7 days, (2) broken down by day, and (3) compared to the week 52 weeks ago:

Since this code block produces efficient joins, users are also able to select unrelated aggregates in an Explore for a given date range to compare them side by side, without causing prohibitive fan-out in the SQL query:

Implementation

The logic in this block requires dialect-specific date functions. Looker does not automatically translate from dialect to dialect. As a result, you may need to adapt the syntax to your specific dialect. The code below is for Redshift.

Beyond dialect adaptation, in order to customize the block for your data, you will need to perform these steps:

  1. Change the connection name to list your desired connection.
  2. Adapt (one or more copies of) the pop_* views in the example. These views define:
    • The underlying table
    • Which date field the table will be joined on
    • The aggregation to apply in the sub-queries
    • The aggregation to apply in the sub-queries
  3. Copy the joins in the pop_explore to point to as many pop_* views as you want to include.

The Code

Starting in Looker 7.4, the filters subparameter syntax has changed. See the always_filter parameter documentation page to view the new syntax.


connection: "your_connection_name"

#explore(pop_explore) is defined below the pop views

view: pop_order_items_created {

view_label: "Order Items (By created)"

#These are FYI - Currently, Looker does not substitute in sql_table_name, so you must do these by hand

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

dimension: date_field {sql: order_items.created_at ;; hidden:yes}

dimension: join_date { sql: DATE_TRUNC({% parameter pop.within_period_type %},${date_field}) ;; hidden:yes }

# Do these substitutions by hand

# sql_table_name: (SELECT

# ${join_date} as join_date

# ${agg_1_inner} as agg_1

# FROM ${SQL_TABLE_NAME}

# GROUP BY ${join_date}

# ) ;;

sql_table_name: (SELECT

DATE_TRUNC({% parameter pop.within_period_type %},order_items.created_at) as join_date,

COUNT(*) as agg_1,

SUM(order_items.sale_price) as agg_2

FROM order_items

WHERE {%condition pop_order_items_created.sale_price %}order_items.sale_price{% endcondition %}

GROUP BY 1

) ;;

#OPTIONAL : Filter inner query on minx/max dates (since query optimizer probably won't)

#You can put aggregates and filters directly in the view file

#Or, you can extend them from another view like this exampme, so you can join the same view on different

# date fields

extends: [pop_order_items_base]

}

view: pop_order_items_delivered {

view_label: "Order Items (By delivered)"

#These are FYI - Currently, Looker does not substitute in sql_table_name, so you must do these by hand

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

dimension: date_field {sql: order_items.shipped_at ;; hidden:yes}

dimension: join_date { sql: DATE_TRUNC({% parameter pop.within_period_type %},${date_field}) ;; hidden:yes }

# Do these substitutions by hand

# sql_table_name: (SELECT

# ${join_date} as join_date

# ${agg_1_inner} as agg_1

# FROM ${SQL_TABLE_NAME}

# GROUP BY ${join_date}

# ) ;;

sql_table_name: (SELECT

DATE_TRUNC({% parameter pop.within_period_type %},order_items.shipped_at) as join_date,

COUNT(*) as agg_1,

SUM(order_items.sale_price) as agg_2

FROM order_items

WHERE {%condition pop_order_items_delivered.sale_price %}order_items.sale_price{% endcondition %}

GROUP BY 1

) ;;

#OPTIONAL : Filter inner query on minx/max dates (since query optimizer probably won't)

#You can put aggregates and filters directly in the view file

#Or, you can extend them from another view like this exampme, so you can join the same view on different

# date fields

extends: [pop_order_items_base]

}

view: pop_order_items_base {

extension: required

filter: sale_price {

type: number

}

# Do this substitutions by hand in sql_table_name

# measure: agg_1_inner {

# hidden: yes

# sql: COUNT(*) ;;

# }

measure: agg_1 {

type: number

label: "Count"

sql: SUM(${TABLE}.agg_1) ;;

}

# Do this substitutions by hand in sql_table_name

#measure: agg_2_inner {

# hidden: yes

# sql: SUM(${SQL_TABLE_NAME}.sale_price) ;;

#}

measure: agg_2 {

type: number

label: "Total Amount"

sql: SUM(${TABLE}.agg_2) ;;

}

}

explore: pop_explore {

from: pop

view_name: pop

join: within_periods { #No editing needed

from: numbers

type: left_outer

relationship: one_to_many

fields: []

sql_on: ${within_periods.n}

<= DATEDIFF( {% parameter pop.within_period_type %},{% date_start pop.date_filter %},{% date_end pop.date_filter %} )

* CASE WHEN {%parameter pop.within_period_type %} = 'hour' THEN 24 ELSE 1 END;;

}

join: over_periods { #No editing needed

from: numbers

view_label: "[PoP]"

type: left_outer

relationship: one_to_many

sql_on:

CASE WHEN {% condition pop.over_how_many_past_periods %} NULL {% endcondition %}

THEN

${over_periods.n} <= 1

ELSE

{% condition pop.over_how_many_past_periods %} ${over_periods.n} {% endcondition %}

END;;

}

#Rename (& optionally repeat) below join to match your pop view(s)

join: pop_order_items_created {

type: left_outer

relationship: many_to_one

#Apply join name below in sql_on

sql_on: pop_order_items_created.join_date = DATE_TRUNC({% parameter pop.within_period_type %},

DATEADD({% parameter pop.over_period_type %}, 0 - ${over_periods.n},

DATEADD({% parameter pop.within_period_type %}, 0 - ${within_periods.n},

{% date_end pop.date_filter %}

)

)

);;

}

join: pop_order_items_delivered {

type: left_outer

relationship: many_to_one

#Apply join name below in sql_on

sql_on: pop_order_items_delivered.join_date = DATE_TRUNC({% parameter pop.within_period_type %},

DATEADD({% parameter pop.over_period_type %}, 0 - ${over_periods.n},

DATEADD({% parameter pop.within_period_type %}, 0 - ${within_periods.n},

{% date_end pop.date_filter %}

)

)

);;

}

#No editing needed below

always_join: [pop,within_periods,over_periods]

always_filter: {

filters: {

field: pop.date_filter

value: "last 12 weeks"

}

filters: {

field: pop.within_period_type

value: "week"

}

filters: {

field: pop.over_period_type

value: "year"

}

}

}

# The below views should not need editing (unless you want to add more than 52 periods)

view: numbers {

sql_table_name: (

SELECT 00 as n UNION ALL SELECT 01 UNION ALL SELECT 02 UNION ALL

SELECT 03 UNION ALL SELECT 04 UNION ALL SELECT 05 UNION ALL

SELECT 06 UNION ALL SELECT 07 UNION ALL SELECT 08 UNION ALL

SELECT 09 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL

SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL

SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL

SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL

SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL

SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL

SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL

SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL

SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL

SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL

SELECT 39 UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL

SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL

SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL

SELECT 48 UNION ALL SELECT 49 UNION ALL SELECT 50 UNION ALL

SELECT 51 UNION ALL SELECT 52 )

;;

dimension: n {

type: number

hidden: yes

sql: ${TABLE}.n ;;

}

}

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_ADD({% parameter pop.within_period_type %},0 - ${within_periods.n},{% date_end pop.date_filter %}));;

}

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

}

}

For further reading on period-over-period modeling, also see the following Help Center articles:

14 39 10.2K
39 REPLIES 39

Where’s the code for the block?

hey @Maximilian_Roos - code has been added, thanks for the interest! Please let us know if you have any trouble implementing 🙂 Happy modeling!

gorden
Participant II

Thanks for this very useful analytic block.

I am still trying to get my head around a number of things that are not clear in the code block.

  1. Commented code blocks that are marked “Do this substitution by hand in sql_table_name” - does one always need to act on this? E.g. in the view pop_order_items_base, the commented code seems to be redundant, or I am missing something?

  2. No sql table or derived table is used in the view “pop_order_items_base” - is this correct?

Hi Gorden,

  1. Yes, at the moment, you must do this. LookML ${substitution} is not implemented in certain fields where it was not expected to be useful, like sql_table_name. As a result, you must write only SQL in sql_table_name, and so you have to manually do substitution. The commented version is left in the block because it serves as the instructions - you manually substitute the hard-coded SQL using that comment block as the template.

  2. That’s correct. I included this view as an example to follow when you have one table with multiple date fields that you may want to group by (like orders.order_date and orders.ship_date). In this case, you want to reuse 95% of the view definition and just change the join_date. To do this, you put the 95% in a “base” view that specifies extension: required, meaning that other views can extend from it, but it cannot be used directly in an explore. Then, you create two views that extend the base view and provide the missing 5%, which in this case includes the missing sql_table_name

  3. I have another powerful pattern for aggregating to dates, which is going to be published next month with the release of 4.14 - stay tuned! 🙂

gorden
Participant II
Hi Fabio,

I seem to be having issues when I explore pop_explore when including dimensions / measures from my equivalent of “pop_order_item_created” in the example. (Excluding these dimensions from “pop_order_item_created” runs without issues.) The error is supposedly a SLQ syntax error but I’ve hit a wall in trying to identify the cause of this. I’ll share with you the relevant code: (NB: I use a use UDF for date_trunc as this does not exist in MySQL, which works as expected)

view: pop_users_created {
  view_label: "Users (By created)"
  sql_table_name: (SELECT
        DATE_TRUNC({% parameter pop.within_period_type %},v_users.creation_timestamp) as join_date,
        COUNT(*) as agg_1
        FROM v_users
        WHERE {% condition pop_users_created.email_verified %}v_users.email_verified{% endcondition %}
        GROUP BY 1
        ) ;;
    extends: [pop_users_base]
  }

The SQL query created (only showing the left join to the above table where the error occurs):

LEFT JOIN (SELECT
        DATE_TRUNC('week',v_users.creation_timestamp) as join_date,
        COUNT(*) as agg_1
        FROM v_users
        WHERE 1=1 -- no filter on 'pop_users_created.email_verified' 

        GROUP BY 1
        )  AS pop_users_created ON pop_users_created.join_date = DATE_TRUNC('week',
          DATE_ADD ( DATE_ADD(DATE_ADD(DATE_ADD(TIMESTAMP(DATE(DATE_ADD(CURDATE(),INTERVAL (0 - MOD((DAYOFWEEK(CURDATE()) - 1) - 1 + 7, 7)) day))),INTERVAL -11 week),INTERVAL 12 week), INTERVAL (0 - within_periods.n)
                  WEEK), INTERVAL (0 - over_periods.n) YEAR
              )
      )

Not sure if this is enough detail to go with but I can share rest of code (essentially code provided in this block with modifications to suit my use case.)

Thanks,
Gorden

gorden
Participant II

This is the error msg (NB: As this is MySQL, I made some changes to the template code to suit MySQL, which work…well, mostly except when the join is evoked.)

Could you come on chat or visit us at help.looker.com ? We’ll be happy to dig into this error message with you.

Hi Gorden! It appears that MySQL doesn’t like the superfluous space between DATE_ADD and its subsequent parenthesis. I guess Redshift doesn’t care much, so my example contained it, but I’ll remove it for the sake of the next person trying to adapt the code. Speaking of which, if you have SQL/LookML you can share here for other MySQL users, that would be great!

gorden
Participant II

Thanks so much Fabio! You saved me from insanity 🙂
I will definitely post the equivalent MySQL code.

Hey Gorden, did you have any luck converting this to MySQL? We have some other clients asking for just that. If you’d like to send it to me privately, I can scrub it of any information that is specific to your model! My email is fabio@

gorden
Participant II

Sorry for the delay in posting the equivalent MySQL code. Unlike the original PostgreSQL/Redshift version, the following version is a bit verbose as MySQL doesn’t have support for some PostgreSQL features. (There could be a better way to make it succinct but I just went with a 1-to-1 direct mapping.)
The first thing one need to is define a DATE_TRUNC user-defined function in MySQL as follows (thanks to http://thenoyes.com/littlenoise/?p=399😞

DELIMITER $$
DROP FUNCTION IF EXISTS DATE_TRUNC $$
CREATE FUNCTION DATE_TRUNC(field ENUM('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'), source datetime(6))
RETURNS datetime(6)
DETERMINISTIC
BEGIN
  IF field IN ('millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) % 1000 MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond') THEN SET source = source - INTERVAL MICROSECOND(source) MICROSECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second') THEN SET source = source - INTERVAL SECOND(source) SECOND; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute') THEN SET source = source - INTERVAL MINUTE(source) MINUTE; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour') THEN SET source = source - INTERVAL HOUR(source) HOUR; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day') THEN SET source = source - INTERVAL DAYOFWEEK(source) - 1 DAY; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'day', 'week') THEN SET source = source - INTERVAL DAY(source) - 1 DAY; END IF;
  IF field IN ('quarter') THEN SET source = source - INTERVAL MONTH(source) % 3 - 1 MONTH; END IF;
  IF field NOT IN ('microsecond', 'millisecond', 'second', 'minute', 'hour', 'week', 'day', 'month', 'quarter') THEN SET source = source - INTERVAL MONTH(source) - 1 MONTH; END IF;
 
  RETURN source;
END $$
DELIMITER ;

In the attached file, I’ve tweaked the pop_* blocks in original post to fit my use case but the logic is exactly the same. The key changes to note are mostly where date and char formatting functions are used.
pop_mysql_functions.rb (23.2 KB)

That’s amazing. Thank you Gorden!!

(Athena specific) 2 fields in view: pop need to be changed to implement this on Amazon Athena due to (a) TO_CHAR date formats and (b) CONCAT() instead of double pipes (||):

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 'mm yy'
      WHEN 'quarter' THEN 'yyyy mm'
      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 hh24'
      ELSE 'mm/dd/yy'
      END)
    ;;}

  dimension: over_periods_ago  {
    label: "Prior Periods"
    description: "Pivot me!"
    sql: CASE ${over_periods.n}
            WHEN 0 THEN CONCAT('Current ',{% parameter pop.over_period_type %})
            WHEN 1 THEN CONCAT(CAST(${over_periods.n} as varchar),' ',{% parameter pop.over_period_type %},' prior')
            ELSE        CONCAT(CAST(${over_periods.n} as varchar),' ',{% parameter pop.over_period_type %}, 's prior')
            END;;
    order_by_field: over_periods.n
  }

Hey, I’ve been trying to adapt your code to test it out in our environment but this error comes up.

“always_join: Unknown Join pop”.

Do you have any idea how to fix it?

Thanks.

Hi @Alberto_Cuenca,

It would be great if you can come on chat and we can help you go through the code and steps with the implementation.

Thanks,

Sasha

We’ve managed to get it right for the moment.

Thanks!!

Hi all - trying to adapt this for PostgreSQL. I’m running into a bit of a snag replacing the DATE_ADD function with the PostgreSQL equivalent.

Has anyone already done this conversion? Having trouble passing the parameters values into the SQL statement, in Postgres it needs to look like this:

YOURDATE + INTERVAL ‘1 day’

I need to wrap the parameter values in single quotes but the way I am doing it isn’t quite right:

    sql: DATE_TRUNC({% parameter pop.within_period_type %},{% date_end pop.date_filter %} - INTERVAL "'"${within_periods.n} {% parameter pop.within_period_type %}"'");;

Hi Mark,

Thanks for that! To better help you with the issue, may I see the the entire code for the parameters you have defined to use in the given SQL? Also, if you could provide me with the SQL generated that would be very helpful. I’m thinking that we may end up making a new dimension that uses CONCAT() and referencing that. Thanks!

Hi Mark,

If you have trouble with this approach, I have another approach that works quite well and is more in the spirit of writing less manual SQL. See this article, and in particular the FAQ at the end about conforming dates:

If you want to build an explore that lets users start from a “one” table and then fan-out into more detailed “many” records from multiple tables, you can often feel constrained by the fact that, by default, these joins will be conflicting. For example, if you have an application where users can order service and then separately make payments against their account, you’ll find that there isn’t a direct relationship between payments and orders, and so you want to let your explore expose both as c…

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
  }
}

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.

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

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.

Is the code in GitHub?

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!

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!

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…

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.

Ipear3
Participant I

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

gorden
Participant II

@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}

Thanks,

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

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)

ajones
Participant I

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

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

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?

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 (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…

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

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?

1bbfe1ae-a34b-4a20-ac56-aee3f2627e6f.png

BIDevSonu
Participant I

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. 

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

Does anyone have the updated github link for Big Query?

Top Labels in this Space
Top Solution Authors