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.
In case anyone wants code which would give you the values only until the given date instead of current date, use a another parameter and as below and change the code as below. The code above if MTD is chosen in OCT and if the date is given as 09/10 instead of 10/05 would return all values for September and sometimes we would like to see values only upto a given date
parameter: to_date {
type: date
}
parameter: time_period {
type: unquoted
allowed_value: {
value: "WTD"
}
allowed_value: {
value: "MTD"
}
allowed_value: {
value: "QTD"
}
allowed_value: {
value: "Last6Months"
}
allowed_value: {
value: "YTD"
}
}
dimension: to_day {
label: "{% parameter time_period %}"
type: yesno
hidden: no
sql:
{% if time_period._parameter_value == 'WTD' %}
${AsofDate} >= TO_DATE(DATE_TRUNC('week', {% parameter Date.to_date %}))
AND
${AsofDate} <= {% parameter Date.to_date %}
{% elsif time_period._parameter_value == 'MTD' %}
${AsofDate} >= TO_DATE(DATE_TRUNC('month', {% parameter Date.to_date %}))
AND
${AsofDate} <= {% parameter Date.to_date %}
{% elsif time_period._parameter_value == 'QTD' %}
${AsofDate} >= TO_DATE(DATE_TRUNC('quarter', {% parameter Date.to_date %}))
AND
${AsofDate} <= {% parameter Date.to_date %}
{% elsif time_period._parameter_value == '6Months' %}
${AsofDate} <= {% parameter Date.to_date %}
AND
${AsofDate} >=({% parameter Date.to_date %}) - INTERVAL '6 month'
{% elsif time_period._parameter_value == 'YTD' %}
${AsofDate} >= TO_DATE(DATE_TRUNC('year', {% parameter Date.to_date %}))
AND
${AsofDate} <= {% parameter Date.to_date %}
{% else %}
1=1
{% endif %};;
}