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. |
The user will pivot on a special new dimension, and will have the option to customize the period length and # of periods to show
Expand to see two other examples including optional parameters
Presume we know the period size and the number of periods the user wants for comparison. We will then:
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:
Same steps in sql/query-ish steps:
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" ) );; } }
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
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
# }
}
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.
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!
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
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).
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
What I need to achieve is shown on the image 2 below:
Image 2
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
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
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
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?
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
etc.
I hope this helps someone else implement this should-be-a-built-in feature.