Use Refinements to add Period over Period functionality to existing explores

Using Refinements to add Period over Period functionality to existing explores

In this example, we’ll use refinements to implement a commonly requested explore feature: Period over Period analysis.














 
Background on Refinements in general Background on Period over Period
With refinements, feature logic can be kept fully separate from core logic, yet can still be added to the original objects (where desired). For example, with this pattern you will be adding Period over Period capability seamlessly onto existing date fields in your existing explores, without complicating your core objects.

For additional explanation and background, see the lookml refinements docs page and Fabio’s Organize Lookml with Refinements.
Period over Period is a oft-requested capability, and several other Looker methods have been described in other articles such as Flexible Period-over-Period Analysis, and An explore filter based approach, among others.

This approach should be simple to implement, and will not need special maintenance if your explore changes. For users, it is flexible without being overly complex.

What Will This Period over Period Interface Look Like?

The user will pivot on a special new dimension, and will have the option to customize the period length and # of periods to show

basic pop example


Expand to see two other examples including optional parameters

Usage

Usage

Benefits of this approach

  • Includes similar benefits to the most comprehensive methods
    • Allows many periods
    • Allows several choices of period sizes
    • Support multiple variations of Pop such as different base date fields, even within the same explore.
     
  • Additional benefits of this method:
    • Layers on top of existing explores and reporting, rather than requiring new explores or many new fields. Existing drills, etc, should play nice
    • Very simple to maintain & scale: No derived table sql that needs to change when new fields are added, and measures do not need to be redefined anywhere for them to work with POP
    • Intuitive for Users: Enable smart period length default
    • Uses Looker automatic date function syntax generation so you should be able to avoid dialect specific syntax changes you may require with other methods 

Conceptual Explanation of this approach for Period over Period

Presume we know the period size and the number of periods the user wants for comparison. We will then:

  1. Make duplicates of the original dataset (conceptually) - one for each period.
  2. Offset the duplicated data according to how many periods ago it needs to represent and the period size.

Result: Each prior period’s data is correctly re-aligned to the corresponding date in the Current Period.

With these steps completed, everything else your in your explore can work smoothly even with PoP applied because the fanned out periods will fall naturally into corresponding pivot columns, and the grain of your results will be otherwise completely unchanged. Everything else will group and calculate just as before.

The same steps represented in an image:
 

Conceptual Daigram

Same steps in sql/query-ish steps:

  1. Join a pop_support view
    • (one row for each selected period, effectively duplicating data for each row)
  2. Refined_Date_Field = Original_Data_Date + ( [pop_support.number_periods_ago] * [Selected Period Length] )
  3. Achieved by wrapping the date offset functions around your date field’s ${EXTENDED} keyword (it’s original sql).

Actual Implementation Steps

Prep) Paste some Period over Period Support LookML in a new file.

Note: This is generic code which you will be able to re-use with any number of pop enabled fields.


Period over Period Support LookML

#You should not need to modify the code below. Save this code in a file and include that file wherever needed (i.e. in your refinement that leverages this pop support logic) view: pop_support { derived_table: { sql: select periods_ago from ( select 0 as periods_ago {% if periods_ago._in_query%}{%comment%}extra backstop to prevent unnecessary fannouts if this view gets joined for any reason but periods_ago isn't actually used.{%endcomment%} {% for i in (1..52)%} union all select {{i}}{%endfor%}{%comment%}Up to 52 weeks. Number can be set higher, no real problem except poor selections will cause a pivot so large that rendering will get bogged down{%endcomment%} {%endif%} ) possible_periods where {%condition periods_ago_to_include%}periods_ago{%endcondition%} {% if periods_ago_to_include._is_filtered == false%}and periods_ago <=1{%endif%}{%comment%}default to only one prior period{%endcomment%} ;; } dimension: periods_ago {hidden:yes type:number} filter: periods_ago_to_include { label: "PoP Periods Ago To Include" description: "Apply this filter to specify which past periods to compare to. Default: 0 or 1 (meaning 1 period ago and 0 periods ago(current)). You can also use numeric filtration like Less Than or Equal To 12, etc" type: number default_value: "0,1" } parameter: period_size { label: "PoP Period Size" description: "The defaults should work intuitively (should align with the selected dimension, i.e. the grain of the rows), but you can use this if you need to specify a different offset amount. For example, you might want to see daily results, but compare to 52 WEEKS prior" type: unquoted allowed_value: {value:"Day"} allowed_value: {value:"Month"} allowed_value: {value:"Year"} # allowed_value: {value:"Week"} # allowed_value: {value:"Quarter"} # other timeframes could be handled with some adjustments, but may not be universally supported for each dialect and may be superfluous to users allowed_value: {value:"Default" label:"Default Based on Selection"} default_value: "Default" } dimension: now_sql { type: date_raw expression: now();; } dimension: now_converted_to_date_with_tz_sql { hidden: yes type: date expression: now();; } dimension: pop_sql_years_using_now {type: date_raw expression: add_years(${periods_ago},${now_sql});;}#use looker expressions to get dialect specific sql for date add functions dimension: pop_sql_months_using_now {type: date_raw expression: add_months(${periods_ago},${now_sql});;} dimension: pop_sql_days_using_now {type: date_raw expression: add_days(${periods_ago},${now_sql});;} dimension: period_label_sql { hidden:yes expression: if(${pop_support.periods_ago}=0," Current" , concat( ${pop_support.periods_ago}," REPLACE_WITH_PERIOD" ,if(${pop_support.periods_ago}>1,"s","") ," Prior" ) );; } }

Step 1) Paste the generic pop_support join in an explore you want to add pop to.


Explore/Join LookML

In our example, we will be refining an existing order_items view, and order_items is the base view of our explore. However, note that your PoP refinement could be similarly added to any existing explore that includes the refined view, regardless of what other joins or features are in the explore.



connection: "your_connection"

explore: order_items {

#your other joins, etc, would typically go here

#To enable pop, you'll paste this join to your explore definition

join: pop_support {

view_label: "PoP Support - Overrides and Tools" #(Optionally) Update view label for use in this explore here, rather than in pop_support view. You might choose to align this to your POP date's view label.

relationship:one_to_one #we are intentionally fanning out, so this should stay one_to_one

sql:{% if pop_support.periods_ago._in_query%}LEFT JOIN pop_support on 1=1{%endif%};;#join and fannout data for each prior_period included **if and only if** lynchpin pivot field (periods_ago) is selected. This safety measure ensures we dont fire any fannout join if the user selected PoP parameters from pop support but didn't actually select a pop pivot field.

}

#(Optionally): Update this always filter to your base date field to encourage a filter. Without any filter, 'future' periods will be shown when POP is used (because, for example: today's data is/will be technically 'last year' for next year)

#always_filter: {filters: [order_items.created_date: "before 0 minutes ago"]}

}

include: "/your_refinement_file" #!Update this to match the file name you used in step 2



Step 2) Refine your view that has the base date field

Paste the refinement template below and then update a few references (to match your existing view name and date field name), after which the prepared PoP feature logic will be applied to your existing date field.


See Refinement Template

Note that, in this code block, the lines where you need to update references to match to your existing objects are left aligned.



include: "/pop_support__template" #include the helper fields that are core to the PoP implementation - meaning: include the file in which you pasted the code as described in Step 1 above.

include: "/your_original_view_file.view" #!Include the file that defines your base view here so you can refine it



view: +order_items {#!Update to point to your view name (with the '+' making it a refinement). That view's file must be included here, and then THIS file must be included in the explore



#Refine YOUR date field by simply updating the dimension group name to match your base date field

dimension_group: created {

convert_tz: no #we need to inject the conversion before the date manipulation

datatype: datetime

sql:{% assign now_converted_to_date_with_timezone_sql = "${pop_support.now_converted_to_date_with_tz_sql::date}" %}{% assign now_unconverted_sql = pop_support.now_sql._sql %}{%comment%}pulling in logic from pop support template, within which we'll inject the original sql. Use $ {::date} when we want to get looker to do conversions, but _sql to extract raw sql {%endcomment%}
{% assign selected_period_size = selected_period_size._sql | strip %}
{%if selected_period_size == 'Day'%}{% assign pop_sql_using_now = "${pop_support.pop_sql_days_using_now}" %}{%elsif selected_period_size == 'Month'%}{% assign pop_sql_using_now = "${pop_support.pop_sql_months_using_now}" %}{%else%}{% assign pop_sql_using_now = "${pop_support.pop_sql_years_using_now}" %}{%endif%}
{% assign my_date_converted = now_converted_to_date_with_timezone_sql | replace:now_unconverted_sql,"${EXTENDED}" %}
{% if pop_support.periods_ago._in_query %}{{ pop_sql_using_now | replace: now_unconverted_sql, my_date_converted }}
{%else%}{{my_date_converted}}
{%endif%};;#wraps your original sql (i.e. ${EXTENDED}) inside custom pop logic, leveraging the parameterized selected-period-size-or-smart-default (defined below)

}



#Selected Period Size sets up Default Period Lengths to use for each of your timeframes, if the user doesn't adjust the PoP period size parameter

#If you only wanted YOY to be available, simply hard code this to year and hide the timeframes parameter in pop support

dimension: selected_period_size {

hidden: yes

sql:{%if pop_support.period_size._parameter_value != 'Default'%}{{pop_support.period_size._parameter_value}}

{% else %}

{% if

created_date._is_selected %}Day

{% elsif

created_month._is_selected %}Month

{% else %}Year

{% endif %}

{% endif %};;#!Update the liquid that mentions created_date and created_month to point to your timeframes, and potentially add more checks for other timeframes, and to consider other pop refined date fields within this view (if any)

}



dimension: created_date_periods_ago_pivot {#!Update to match your base field name. This is generic sql logic (so you might expect it to be in pop_support template), but it is helpful to manifest this lynchpin pivot field here so we can create a dedicated pivot field in this specific date dimension's group label.

label: "{% if _field._in_query%}Pop Period (Created {{selected_period_size._sql}}){%else%} Pivot for Period Over Period{%endif%}"#makes the 'PIVOT ME' instruction clear in the field picker, but uses a dynamic output label based on the period size selected

group_label: "Created Date" #!Update this group label if necessary to make it fall in your date field's group_label

order_by_field: pop_support.periods_ago #sort numerically/chronologically.

sql:{% assign period_label_sql = "${pop_support.period_label_sql}" %}{% assign selected_period_size = selected_period_size._sql | strip%}{% assign label_using_selected_period_size = period_label_sql | replace: 'REPLACE_WITH_PERIOD',selected_period_size%}{{label_using_selected_period_size}};;#makes intuitive period labels

}



# Optional Validation Support field. If there's ever any confusion with the results of PoP, it's helpful to see the exact min and max times of your raw data flowing through.

# measure: pop_validation {

# view_label: "PoP - VALIDATION - TO BE HIDDEN"

# label: "Range of Raw Dates Included"

# description: "Note: does not reflect timezone conversion"

#sql:{%assign base_sql = '${TABLE}.created_at'%}concat(concat(min({{base_sql}}),' to '),max({{base_sql}}));;#!Paste the sql parameter value from the original date fields as the variable value for base_sql

# }

}

Wrapup) Validate and ensure includes statements are updated as necessary based on your specific file names.

Known Challenges

  • If your base date field’s sql include double quotes, this can impact the liquid based formula construction. Double quotes should be avoidable in most dialects, by using [ ] instead of quotes or renaming the physical column without reserved characters.
  • We have used lookml_field_type_references ::date and ::datetime to pass sql logic between fields with converting dates to strings.  These references have surfaced datatype errors in certain dialects with certain base data types. Depending on your dialect and raw date field’s datatype, you may need to switch ::datetime to ::date or in some cases you can remove the ::datetime or ::date entirely.
     

Expand for additional complexities and considerations

- This process will show ‘Prior data for future periods’ which have not yet come to pass. Technically this is an accurate representation of the data but may be distracting to users. You may choose to apply the optional always filter or some other filtration to suppres this.

- Timezone conversion needs to happen BEFORE date manipulation in order to maintain correct groupings, so we apply timezone conversion with liquid instead of letting looker do convert_tz:yes. This adds to the complexity of the refinement logic, and may be removeable if you don't use timezone conversions, though it should do no harm.

- PoP functionality can be added on additional date fields, though it requires some care to avoid name collisions on pop pivot dimensions you create.

- Watch out for Database syntax differences... Although we have used looker expressions to leverage looker's built in dialect specific date function handling, some dialects may have other limitations not yet identified such as complaints about the fannout join (you may try type:cross instead), etc.

Closing

I hope this pattern helps your team quickly implement Period over Period functionality without complicating your base code or adding new explores just for PoP.

Additionally, I hope this inspires you to use refinements for better code organization and feature management.

Let us know how you used this pop approach or refinements in your case!

15 14 4,905
14 REPLIES 14

mike1231
Participant I

We’ve been trying this and it looks really useful. However we’ve been struggling to get it to work if BigQuery when the table has Require partition filter turned on (which most of our tables do). For partition pruning BQ does not like it when, in the where clause, the dt is pruned using a non-constant expression. In the where clause we get something like this:

DATE_ADD(dt , INTERVAL pop_support.periods_ago MONTH)

which Bigquery can’t use to prune it’s partitions as the pop_support.periods_ago isn’t constant.
https://cloud.google.com/bigquery/docs/querying-partitioned-tables#pruning_limiting_partitions .

For tables that don’t have the require partition filter option the query results in a full table scan as BQ can prune any partitions.

One way we’re looking to get round this is to create a derived date table, which we create using Liquid, that has the absolute value of the periods_ago rather than being a reference, and then inner joining that to our fact table.

Not sure if anyone else has attempted this or has a work around/suggestions for BQ?

We’ve been trying this and it looks really useful. However we’ve been struggling to get it to work if BigQuery when the table has Require partition filter turned on (which most of our tables do). For partition pruning BQ does not like it when, in the where clause, the dt is pruned using a non-constant expression. In the where clause we get something like this:

DATE_ADD(dt , INTERVAL pop_support.periods_ago MONTH)

which Bigquery can’t use to prune it’s partitions as the pop_support.periods_ago isn’t constant.
https://cloud.google.com/bigquery/docs/querying-partitioned-tables#pruning_limiting_partitions .

For tables that don’t have the require partition filter option the query results in a full table scan as BQ can prune any partitions.

One way we’re looking to get round this is to create a derived date table, which we create using Liquid, that has the absolute value of the periods_ago rather than being a reference, and then inner joining that to our fact table.

Not sure if anyone else has attempted this or has a work around/suggestions for BQ?

Hello Mike. 

As you’ve mentioned, this solution uses date_add sql functions as a core component. Date add functions are supported by a variety of sql dialects, and allows us to rely on the database to handle the complexities, things like adding Months when there’s leap day involved, etc.  Another benefit of this approach is that normal Looker filters set on the refined date field automatically work as you would expect: limiting raw data to the selected period and the ‘duplicates of the original dataset for prior periods’ to the corresponding records.

That said, for your specific scenario, it appears that we would need filters applied directly to the partitioned field, and so we will need filters to be different and specific for every ‘duplicate of the original dataset for prior period’. 


Full disclosure, I have not implemented this approach, but I do see a path forward. The path involves replacing the cross-join component of this solution with a series of unions, each filtered on the partition field.  You would update the source of the view that the date field is in, using liquid to generates a union statement for each prior period (not dissimilar to how the cross join’s source uses a liquid for loop). 

Unfortunately, the challenge then will be to set the correct filter range for each union statement (without relying date_add in the filter) - instead of offsetting the raw dates and then filtering, we need to offset the start_date and end_date of the user’s filter, in the sql generation itself, also using liquid (see https://docs.looker.com/reference/liquid-variables).  There is no date_add equivalent in liquid which allows you specify date-parts - so I believe you’ll need to either

  1. decompose, manipulate, and recompose components of the date (which is likely prone to edge case problems like you’ll offset leap day to a prior year where 2/29 does not exist), or
  2. limit support to timeframes that can be defined in terms of a consistent number of seconds, like days, weeks and ‘52 week years’.  For this you will use use the %s liquid date format, add seconds corresponding to the period length, and then reset to filterable standard sql date format.

I hope these ideas help!  Let us know how it goes!

Hi Kevin,

This is a great help, I really like how it incorporates the Pop elements into the original date dimension group. 
I’m having issues implementing this, particularly using “${EXTENDED}” within Liquid. I’m getting a LookML Depreciation warning:

“This liquid does not conform to the Liquid language spec and will produce warnings or errors in a future version of Looker. Parse error was: Error parsing liquid: Liquid parse exception: parser error "extraneous input 'TRANSACTION_DATE' expecting {TagEnd, '|'}"
(with TRANSACTION_DATE being my original dimension group)

Furthermore, using only the original date field without a pivot in Explore fails. I can see in the SQL compilation error that it’s failing to properly passing the Extended sql from the original.  Any ideas on how ${EXTENDED} should now be formatted within liquid ?

Hello Dave,

I’m not 100% sure but this sounds like it relates to this issue below (copied from known issues section).  

  • If your base date field’s sql include double quotes, this can impact the liquid based formula construction. Double quotes should be avoidable in most dialects, by using [ ] instead of quotes or renaming the physical column without reserved characters.

Please let us know whether this helps resolve the issue

Hello guys,

I’m running into some issues which appears to be fanning out whenever I try to compare any PoP period size by hours as shown on image 1:

Image 1

b82b74b8-5e5f-4087-b7a8-0110094eabeb.png

What I need to achieve is shown on the image 2 below:

Image 2

083811a1-b3fa-4bba-af36-90ae2bfe950b.png

Has any of you stumbled across this situation and have worked out a solution which could help me. Perharps @Kevin_McCarthy  could share an update handling this issue.

Any help at all would be much appreciated.

I’m running into some issues which appears to be fanning out whenever I try to compare any PoP period size by hours as shown on image 1:

Image 1

b82b74b8-5e5f-4087-b7a8-0110094eabeb.png

Hello Lucas,
Looking at this explore UI, it appears to me that you don’t have any date filter applied.  The POP configuration itself doesn’t assume you wanted to look at only one Month, and doesn’t force any filtration. 

Without any Date Filter:

Your Current column is ‘Sessions (all time) by hour of day’
Your 1 Month Prior column is ALSO ‘Session (all time) by hour of day’ (Note: the offset by month has no impact on hour of day)

Assuming you’ve added POP functionality to Session Date, if you add Session Month in Last 1 Complete Month, you Current column would include sessions only for the last completed month, and 1 Month Prior column would include only sessions from 1 Month Prior.  I mention COMPLETE month to emphasize that if you use last 1 Month, your prior period may include data for the last few days of the month for which there’s no current data.  If you want Current month to date vs Prior month to date, you could add a second criteria like session_day is before today.

I hope this helps. Let me know if you are still stuck.

Best
Kevin

BIDevSonu
Participant I

Thanks for sharing.  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. 

Hello BIDevSonu,

If I understand correctly, you don’t necessarily need period over period, but rather you want to let your user pick one quarter and then filter to (that quarter or any of 4 quarters leading up to that quarter).

To me, this sounds like a good case for a parameter (https://docs.looker.com/reference/field-params/parameter).  With parameters, users can enter something in filter bar, and then you can do things based on their selection other than just direct filtration.

The way your data is structured, you’ll have to do a little logic to get to the specific quarters to include.  Effectively, you can parse out their selection and understand their selected year and quarter number, then you want to filter on
((Year = Selected Year and quarter <= selected quarter) or (Year = Selected Year - 1 and quarter >= selected quarter)). 

I would suggest a yesno field that checks that criteria, and then you would filter that yesno field to Yes in the tile specification on the dashboard.

I hope this helps. Let us know if I missed the goal or if you are having trouble with this solution.

Best,

Kevin

BIDevSonu
Participant I

Thanks @Kevin_McCarthy for the details. Will try it out and let you know how it goes!

Hello @Kevin_McCarthy !

Impressed by the simplicity of this solution, I have been looking for something of this kind for some time now

I am facing a problem : as my base model is already extended in another model, joining the pop_support template raises an error. 
However fixing this is not interesting to me as I would prefer for now to build this POP feature on top and in parallel of my current explore : that leads me to the conclusion that, where you use refinement, I would like to use an extends

Would you have any thought on this ? I have been trying for now, extending my base explore and joining pop_support to this new extension but I am missing something. 
 

Thank you !

Has anyone implemented this for BQ dialect? 

@mike1231 did you ever solve this problem with Bigquery implementation?

mike1231
Participant I

Yeah we got it working by inner joining the fact table to a list of dates that we generate, or join it to a date dimension table if there’s no PoP fields selected. This join is performed on every query regardless of if the PoP is being used in the query or not. We could not find a way using liquid to get it to only join if you include a pop field. But we think the join has minimal performance implications so it’s fine for us.

The LookML is very similar to the original post.

Sorry for the massive wall of code….

First we define a pop_date.view, which generates a range of dates, if the fields are not selected it defaults to use a date table:

view: pop_date {
  view_label: "Period Over Period"
  derived_table:{
    sql:
      {% if periods_ago._in_query == true%}
          {% assign period = _filters['periods_ago_to_include'] | split: ","  %}
          {% assign range = period_size._parameter_value %}

          WITH
            periods_ago AS
            (
              SELECT
                periods_ago
              FROM
                (

                {% for i in period %}
                      SELECT {{i}} AS periods_ago
                      {% if forloop.index != period.size %} UNION ALL {%endif%}
                {%endfor%}
                )
              )
          SELECT
          GENERATE_UUID() as uuid,
            periods_ago,
            full_date,
            DATE_ADD(full_date, INTERVAL periods_ago.periods_ago {{range}}) AS offset_date,
            MIN(full_date) OVER(PARTITION BY periods_ago.periods_ago order by full_date) AS period_start_date,
            MAX(full_date) OVER(PARTITION BY periods_ago.periods_ago order by full_date desc) AS period_end_date,
            CAST(DATE_DIFF(full_date,  MIN(full_date) OVER(PARTITION BY periods_ago.periods_ago order by full_date), DAY) AS INT64) AS days_since_start,
            CONCAT(CASE WHEN periods_ago.periods_ago =0 THEN "Current {{range}} "  WHEN periods_ago.periods_ago =1 THEN "Previous  {{range}} " ELSE CONCAT(periods_ago.periods_ago, " {{range}}s ago ") END, CAST(MIN(full_date) OVER(PARTITION BY periods_ago.periods_ago order by full_date) AS STRING), " to ",CAST(MAX(full_date) OVER(PARTITION BY periods_ago.periods_ago order by full_date desc) AS STRING)) date_period
          FROM
            periods_ago
          CROSS JOIN
           some-date-dimension-table

          {% for i in period %}
                    {% if forloop.index ==1 %} WHERE  {%endif%}
                    (full_date  >= DATE_ADD(CAST (IFNULL({% date_start @{fact_table}.@{fact_date} %},CURRENT_DATE()) AS DATE), INTERVAL -{{i}} {{range}})
                    AND
                    full_date  < DATE_ADD(CAST (IFNULL({% date_end @{fact_table}.@{fact_date} %},CURRENT_DATE()) AS DATE), INTERVAL -{{i}} {{range}}) AND  periods_ago.periods_ago={{i}})
                    {% if forloop.index != period.size %} OR  {%endif%}
          {%endfor%}

        {%else%}
          SELECT * FROM some-date-dimension-table
      {%endif%}

      ;;
  }

  dimension: fake_pk {
    primary_key: yes
    sql: ${TABLE}.uuid ;;
  }

  dimension: periods_ago {
    hidden: yes
    sql: ${TABLE}.periods_ago.periods_ago ;;
  }

  dimension: period_start_date {
    hidden: yes
    sql: ${TABLE}.period_start_date ;;
  }

  dimension: period_end_date {
    hidden: yes
    sql: ${TABLE}.period_end_date ;;
  }

  dimension: date_period {
    label: "3. Pivot for Period Over Period"
    sql:  ${TABLE}.date_period ;;
    order_by_field: periods_ago
  }

  dimension: offset_date {
    hidden: yes
    sql: ${TABLE}.offset_date ;;
  }

  dimension: days_since_start {
    type: number
    sql: ${TABLE}.days_since_start ;;
  }


###### POP paramters ###############

  filter: periods_ago_to_include {

    label: "1. PoP Periods Ago To Include"

    description: "Apply this filter to specify which past periods to compare to. Default: 0 or 1 (meaning 1 period ago and 0 periods ago(current)).  You can also use numeric filtration like Less Than or Equal To 12, etc"

    type: number

    default_value: "0,1"

  }

  parameter: period_size {

    label: "2. PoP Period Size"

    description: "The defaults should work intuitively (should align with the selected dimension, i.e. the grain of the rows), but you can use this if you need to specify a different offset amount.  For example, you might want to see daily results, but compare to 52 WEEKS prior"

    type: unquoted

    allowed_value: {value:"Day"}

    allowed_value: {value:"Week"}

    allowed_value: {value:"Month"}

    allowed_value: {value:"Year"} # allowed_value: {value:"Week"} # allowed_value: {value:"Quarter"} # other timeframes could be handled with some adjustments, but may not be universally supported for each dialect and may be superfluous to users

    default_value: "Day"

  }

Refine the fact view to include the PoP date field:

include: "/views/your_fact_view_here.view.lkml"


view: +your_fact_view_here {
  #!Update to point to your view name (with the '+' making it a refinement).  That view's file must be included here, and then THIS file must be included in the explore

  # #Refine YOUR date field by simply updating the dimension group name to match your base date field
  dimension_group: dt{
    type: time
    datatype:  date
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    ## Replace the table.dt with the name of your date
    sql: {% if pop_date.periods_ago._in_query %} ${pop_date.offset_date} {%else%} ${TABLE}.dt  {%endif%} ;;
    #sql:  ${TABLE}.dt  ;;
  }

  dimension_group: pop_date_to_join {
    sql: ${TABLE}.dt ;;
    type: time
    datatype:  date
  }

}


Refine your explore so that it is joining to your PoP_date view. Note that for BQ to partition prune you need to use the raw date when joining. BQ doesn’t partition prune if you join using a casted date fields.
 

include: "pop_date.view.lkml"
include: "/@{model_name}.model.lkml" #!Include the file that defines your base view here so you can refine it
## Contains refinements for the explore and the fact table view
# update this name with your own explore
explore: +your_explore_here {

  join: pop_date {
    relationship: many_to_one
    type: inner
    sql_on:  ${pop_date.full_date} = ${your_explore_here.pop_date_to_join_raw} ;;
  }

}
 

We define our fact table and other values needed in the manifest.lkml so that people can copy paste the code to their projects and just change the fields in one place:

project_name: "your_project"

## define the names of your tables, models and explores for the PoP block here


constant: fact_table {
  value: "your_fact"
}

# the date in your fact table
constant: fact_date {
  value: "dt_date"
}

constant: model_name {
  value: "your_model"
}

constant:  explore_name{
  value: "your_explore"
}
 

Make sure to include your newly created PoP refinements and view in your model:

include: "block_PoP/*.lkml"

Thank you for this!! I implemented this in our org with some changes. (e.g., we don't utilize a manifest, we have built-in fiscal support under some conditions, we want the option of comparing an incomplete period to a complete period like YTD vs full last year)

 

view: pop_date {
  # Takes in Periods ago to include and period size to create a calendar table that includes the actual date and a date offset by those inputs.
  # They are then cross-joined, resulting in a table that can be inner joined to the main query to produce PoP.
  view_label: " Period Over Period"
  derived_table:{
    sql:
    {%- if periods_ago._in_query == true -%}
    -- These two period assigns scream that you only need one, 
    -- but I was having trouble with the commmas
    {%- assign period = periods_ago_to_include._parameter_value | remove: "'" | split: "," -%}
    {%- assign period_comma = periods_ago_to_include._parameter_value | remove: "'" -%}
    {%- assign range = period_size._parameter_value -%}
    -- This UNNEST cleans up the formatted query a tad.
    WITH periods_ago AS(SELECT periods_ago FROM(
      SELECT 
        * 
      FROM UNNEST(GENERATE_ARRAY(0, 365)) AS periods_ago 
      WHERE periods_ago IN ({{period_comma}}) 
      {% if periods_ago_to_include._is_filtered == false -%} AND periods_ago = 0 
      {% endif -%})
    )
    SELECT
      GENERATE_UUID() as uuid,
      periods_ago,
      DATE(c.date) AS full_date,
      DATE_ADD(c.date, INTERVAL periods_ago.periods_ago {{range}}) AS offset_date,
      DATE_ADD(c.date, INTERVAL periods_ago.periods_ago * 52 Week) AS offset_date_fiscal,
      CASE WHEN periods_ago.periods_ago = 0 THEN 'Current'
      ELSE CONCAT(
        periods_ago.periods_ago,
        ' {{pop_date.period_size._parameter_value}}', 
        CASE WHEN periods_ago.periods_ago > 1 THEN 's' ELSE '' END,' Prior')
      END AS date_period
    FROM periods_ago
    CROSS JOIN calendar_table_your_org_hopefully_has c
    {%- else -%} SELECT * FROM calendar_table_your_org_hopefully_has {%- endif -%};;
  }

  dimension: fake_pk {
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.uuid ;;
  }

  dimension_group: full_date {
    type: time
    datatype: date
    hidden: yes
    timeframes: [raw,date,week,month,quarter,year]
    sql: ${TABLE}.full_date ;;
  }

  dimension: periods_ago {
    hidden: yes
    sql: ${TABLE}.periods_ago.periods_ago ;;
  }

###### POP paramters ###############

  parameter: periods_ago_to_include {
    # You can make this open-ended with these as suggestions
    # but I'm keeping it simple for now.
    type: string
    label: "1. PoP Periods Ago To Include"
    description: "Dictates how many periods ago to query."
    allowed_value: {value: "0,1"                          label: "1 period ago"}
    allowed_value: {value: "0,1,2"                        label: "2 periods ago"}
    allowed_value: {value: "0,1,2,3"                      label: "3 periods ago"}
    allowed_value: {value: "0,1,2,3,4"                    label: "4 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5"                  label: "5 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5,6"                label: "6 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5,6,7"              label: "7 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5,6,7,8"            label: "8 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5,6,7,8,9"          label: "9 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5,6,7,8,9,10"       label: "10 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5,6,7,8,9,10,11"    label: "11 periods ago"}
    allowed_value: {value: "0,1,2,3,4,5,6,7,8,9,10,11,12" label: "12 periods ago"}
    default_value: "0,1"
  }

  parameter: period_size {
    label: "2. PoP Period Size"
    description: "This determines the size of Period Ago. Choose 'Year' For Year over Year."
    type: unquoted
    allowed_value: {value:"Day"}
    allowed_value: {value:"Week"}
    allowed_value: {value:"Month"}
    allowed_value: {value:"Quarter"}
    allowed_value: {value:"Year"}
    default_value: "Year"
  }

  dimension: date_period {
    label: "3. Pivot for Period Over Period"
    description: "Pivot by this field to display PoP."
    sql:  ${TABLE}.date_period ;;
    order_by_field: periods_ago
  }

  parameter: is_ytd {
    group_label: "Advanced Parameters"
    label: "4. is YTD?"
    type: unquoted
    description: "Choose Yes for YTD comparison. Choose No for Full Year comparisons. When choosing Yes, the latest queryable date is 12/31 of the current year, allowing full prior periods to be compared to partial current period. When choosing No, the latest queryable date is yesterday--prior periods will only go as far as a year ago yesterday."
    allowed_value: {value:"Yes"}
    allowed_value: {value:"No"}
    default_value: "No"
  }

  ## Helper date dimensions
  # Not necessary, but either make the lookml cleaner or help the user perform non-essential functions.

  dimension_group: yesterday {
    hidden: yes
    datatype: date
    type: time
    timeframes: [date]
    sql: DATE_SUB(CURRENT_DATE(), INTERVAL 1 Day) ;;
  }

  measure: min_date {
    # only purpose is days_included_range
    type: date
    hidden: yes
    datatype: date
    sql: MIN(${TABLE}.full_date) ;;
  }

  measure: max_date {
    # only purpose is days_included_range
    type: date
    hidden: yes
    datatype: date
    sql: MAX(${TABLE}.full_date) ;;
  }
  measure: days_included_range {
    #  In-Explore troubleshooting measure.
    group_label: "Troubleshoot Dates Here"
    description: "Range of Dates included in this period. Use this if you are uncertain which dates are in each row-column combination."
    type: string
    sql:
        CASE WHEN ${min_date}<${max_date} THEN concat(${min_date},' - ',${max_date})
        ELSE CAST(${min_date} AS string)
        END ;;
  }

}

 

Instead of refining the view I'm putting the logic straight in the view itself. 6 of one, half a dozen of the other.

 

view: view_name {
...
filter: date_filter {
    type: date
    hidden: no
    # you can use this elsewhere, but being explicit for users.
    label: "Date Filter (USE FOR POP)"
    description: "USE THIS when filtering by date for PoP"
}
  # These dimensions could benefit from edge-case work
  dimension: filter_start {hidden:yes sql: {% date_start date_filter %} ;;}
  dimension: filter_end {hidden:yes sql:{% date_end date_filter %} ;;}

  # adding dynamic date is really helpful for end users that would like flexibility in their dashboards
  parameter: date_granularity {
    type: unquoted
    group_item_label: "Dynamic Date Granularity"
    description: "Pair this with Dynamic Date. Dictates which date grain is used."
    allowed_value: { value: "Day" }
    allowed_value: { value: "Week"}
    allowed_value: { value: "Month" }
    allowed_value: { value: "Quarter"}
    allowed_value: { value: "Year" }
  }
  # other guides online have this as a dimension but I found that in this implementation
  # it caused some issues with mid-week/mid-month days showing up with null values.
  dimension_group: dynamic {
    group_label: " Dashboard Support"
    # Maybe you can change this, but I'm scarred. setting to yes risks mid-week/mid-month days showing up with null values
    allow_fill: no #DO NOT CHANGE
    timeframes: [date]
    description: "Use this dynamic date grain when you want your date grain to match offset_size."
    # dynamic label shows on rendered results
    label: "{% if date_granularity._is_filtered %}{{view_name.date_granularity._parameter_value}}{% else %}Dynamic Date{% endif%} "
    sql:
     {%- if date_granularity._parameter_value == 'Month' -%}
        FORMAT_TIMESTAMP('%Y-%m',${event_timestamp_month::date})
     {%- elsif date_granularity._parameter_value == 'Week' -%}
        ${event_timestamp_week::date}
     {%- elsif date_granularity._parameter_value == 'Quarter' -%}
        FORMAT_TIMESTAMP('%Y-%m',${event_timestamp_quarter::date})
     {%- elsif date_granularity._parameter_value == 'Day' -%}
        ${event_timestamp_date::date}
     {%- else -%}EXTRACT(YEAR FROM ${event_timestamp_year::date}){%- endif -%}
      ;;
  }

  dimension_group: event_timestamp {
    type: time
    # I don't define datatype because of similar issues related to the above comment on autofill
    # I don't currently support PoP for more than day, week, month, quarter, or year, 
    # but if you're choosing week of year you might as well just pivot by year instead.
    timeframes: [raw,date,day_of_year,day_of_month,day_of_week,week,week_of_year,month,
      month_name,month_num,fiscal_month_num,quarter,fiscal_quarter,year,fiscal_year]
    # Additional logic in case you want to conditionally do a fiscal year ago 
    # when you are doing YoY and choose date or week as your granularity
    sql:
    {%- if (
      pop_date.periods_ago_to_include._in_query 
      and pop_date.period_size._parameter_value == "Year" 
      and (
        view_name.event_timestamp_date._in_query 
        or view_name.event_timestamp_week._in_query
      ) )
      or (
      pop_date.periods_ago_to_include._in_query 
      and pop_date.period_size._parameter_value == "Year" 
      and view_name.dynamic._is_selected 
      and (
        view_name.date_granularity._parameter_value == 'Day' 
        or view_name.date_granularity._parameter_value == 'Week')) -%}
      pop_date.offset_date_fiscal
    {%- elsif pop_date.periods_ago._in_query == true -%}
      pop_date.offset_date
    {%- else -%}${TABLE}.event_timestamp  {%- endif -%} ;;
  }

  dimension_group: pop_date_to_join {
    type: time
    datatype: date
    timeframes: [raw]
    sql: ${TABLE}.event_timestamp ;;
  }

 

And then I found that including the INNER JOIN in `pop_date` wasn't lowering our query costs so I threw it in here.

 

explore: explore_name{
  join: pop_date {
    relationship: many_to_one
    # Note that the below join does not work 100% for leap year.
    sql:
    INNER JOIN pop_date
    ON DATE(${pop_date.full_date_raw}) = DATE(${view_name.pop_date_to_join_raw})
    {%- assign period = pop_date.periods_ago_to_include._parameter_value | remove: "'" | split: ',' -%}
    {%- assign range = pop_date.period_size._parameter_value %}
    AND (
    {%- for i in period %}
    (periods_ago.periods_ago={{i}}
      {% if range == "Year" 
        and (view_name.event_timestamp_date._is_selected 
          or view_name.event_timestamp_week._is_selected 
          or view_name.date_granularity._parameter_value == "Day" 
          or view_name.date_granularity._parameter_value == "Week") -%}
    AND DATE(${view_name.pop_date_to_join_raw}) >= DATE_ADD(DATE_SUB(
      CAST(IFNULL(DATE(${view_name.filter_start}),${pop_date.yesterday_date}) AS DATE), 
      INTERVAL {{i}} {{range}}),
      INTERVAL {{i}} Day)
      {%- else -%}
    AND DATE(${view_name.pop_date_to_join_raw}) >=  DATE_SUB(
      CAST(IFNULL(DATE(${view_name.filter_start}),${pop_date.yesterday_date}) AS DATE), 
      INTERVAL {{i}} {{range}})
      {%- endif %}
      {% if range == "Year" 
        and pop_date.is_ytd._parameter_value == "Yes" 
        and (
          view_name.event_timestamp_date._is_selected 
          or view_name.event_timestamp_week._is_selected
          or view_name.date_granularity._parameter_value == "Day"
          or view_name.date_granularity._parameter_value == "Week") -%}
    AND DATE(${view_name.pop_date_to_join_raw}) <= DATE_ADD(LAST_DAY(DATE_SUB(
      CAST(IFNULL(DATE(${view_name.filter_end}),${pop_date.yesterday_date}) AS DATE), 
      INTERVAL {{i}} {{range}}),
      Year),
      INTERVAL {{i}} Day)
      {% elsif pop_date.is_ytd._parameter_value == "Yes" -%}
    AND DATE(${view_name.pop_date_to_join_raw}) <= LAST_DAY(DATE_SUB(
      CAST(IFNULL(DATE(${view_name.filter_end}),${pop_date.yesterday_date}) AS DATE), 
      INTERVAL {{i}} {{range}}),
      Year)
      {%- else -%}
    AND DATE(${view_name.pop_date_to_join_raw}) < DATE_ADD(DATE_SUB(
      CAST(IFNULL(DATE(${view_name.filter_end}),${pop_date.yesterday_date}) AS DATE),
      INTERVAL {{i}} {{range}}), 
      INTERVAL {{i}} Day)
      {%- endif -%}
      ){% if forloop.index != period.size -%} OR {%- endif -%}
    {%- endfor -%}) ;;
  }
}

 

 I'm sure there's a way of making this DRY using the original method in this post but I couldn't be bothered. Also, though setting it up this way isn't DRY it does set up the opportunity to allow this to function for explores that have more than one date field if you

  1. add the same liquid logic for each date dimension_group
  2. add those extra dates into liquid if statements in the INNER JOIN block and `dynamic` liquid logic
  3. create a `date_picker` parameter to choose the date field you're picking (if you want dynamic date to work)
  4. give `pop_date_to_join` logic to look for each of the available dimension_groups

etc.

I hope this helps someone else implement this should-be-a-built-in feature.

Top Labels in this Space
Top Solution Authors