View the original community article here
Last tested: Jun 30, 2020
Check out Methods for period over period (PoP) analysis in Looker for more methods and examples - this is an example of Method 2.
An example framework for "to day" type analysis (this is specific to Snowflake, so the date functions will need to be adapted to the dialect you are leveraging).
View File Logic:
parameter: time_period {
type: unquoted
allowed_value: {
value: "WTD"
}
allowed_value: {
value: "MTD"
}
allowed_value: {
value: "QTD"
}
allowed_value: {
value: "YTD"
}
}
dimension: is_wtd {
type: yesno
hidden: yes
sql: ${created_raw} > TO_DATE(DATE_TRUNC('week', CURRENT_DATE())) AND ${created_raw} < TO_DATE(DATEADD('day', 7, DATE_TRUNC('week', CURRENT_DATE()))) ;;
}
dimension: is_mtd {
type: yesno
hidden: yes
sql: ${created_raw}> TO_DATE(DATE_TRUNC('month', CURRENT_DATE())) AND ${created_raw} < TODATE(DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE()))) ;;
}
dimension: is_qtd {
type: yesno
hidden: yes
sql: ${created_raw} > TO_DATE(DATE_TRUNC('quarter', CURRENT_DATE())) AND ${created_raw} < (TO_DATE(DATEADD('month', 3, CAST(DATE_TRUNC('quarter', CAST(DATE_TRUNC('quarter', CURRENT_DATE()) AS DATE)) AS DATE)))) ;;
}
dimension: is_ytd {
type: yesno
hidden: yes
sql: ${created_raw} > TO_DATE(DATE_TRUNC('year', CURRENT_DATE())) AND ${created_raw} < TO_DATE(DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE()))) ;;
}
Explore/Model File logic:
explore: inventory_items {
always_filter: {
filters: {
field: time_period
value: "MTD"
}
}
sql_always_where:
{% if inventory_items.time_period._parameter_value == 'WTD' %} ${is_wtd}
{% elsif inventory_items.time_period._parameter_value == 'MTD' %} ${is_mtd}
{% elsif inventory_items.time_period._parameter_value == 'QTD' %} ${is_qtd}
{% elsif inventory_items.time_period._parameter_value == 'YTD' %} ${is_ytd}
{% else %} 1=1
{% endif %}
;;
}
If you want a consolidated approach without having to create a dimension for each "to day" function please note the liquid syntax below.
View File:
parameter: time_period {type: unquoted
allowed_value: {
value: "WTD"
}
allowed_value: {
value: "MTD"
}
allowed_value: {
value: "QTD"
}
allowed_value: {
value: "YTD"
}
}
dimension: to_day {
label: "{% parameter time_period %}"
type: yesno
hidden: yes
sql:
{% if time_period._parameter_value == 'WTD' %}
${created_raw} > TO_DATE(DATE_TRUNC('week', CURRENT_DATE()))
AND
${created_raw} < TO_DATE(DATEADD('day', 7, DATE_TRUNC('week', CURRENT_DATE())))
{% elsif time_period._parameter_value == 'MTD' %}
${created_raw} > TO_DATE(DATE_TRUNC('month', CURRENT_DATE()))
AND
${created_raw} <TO_DATE(DATEADD('month', 1, DATE_TRUNC('month', CURRENT_DATE())))
{% elsif time_period._parameter_value == 'QTD' %}
${created_raw} > TO_DATE(DATE_TRUNC('quarter', CURRENT_DATE()))
AND
${created_raw} < (TO_DATE(DATEADD('month', 3, CAST(DATE_TRUNC('quarter', CAST(DATE_TRUNC('quarter', CURRENT_DATE()) AS DATE)) AS DATE))))
{% elsif time_period._parameter_value == 'YTD' %}
${created_raw} > TO_DATE(DATE_TRUNC('year', CURRENT_DATE()))
AND
${created_raw} < TO_DATE(DATEADD('year', 1, DATE_TRUNC('year', CURRENT_DATE())))
{% else %}
1=1
{% endif %};;
}
Explore/Model File:
explore: inventory_items {
always_filter: {
filters:
field: time_period
value: "MTD"
}
}
sql_always_where:
${to_day}
;;
}
This content is subject to limited support.