View the original community article here
Last tested: May 2020
The Problem
You want to give your users a single date selector, where they can choose a month. Then, when they select a month, they should see data from that month and the previous month.
Filtering for this month on a date dimension will remove any data from the past month, unfortunately. How to show both?
The Solution
Assume ${date_raw}
is the date you want to filter on. (Make sure to use the raw
timeframe if it's a dimension group!)
First, create a filter
field for the user to interact with. Remember that a filter
field doesn't immediately update the WHERE
clause, but can be targeted with Liquid.
filter: date_selector {
type: date
}
Second, create a yesno
dimension that captures the value of date_selector
using liquid. This will be your "this month" indicator.
dimension: is_selected_month {
type: yesno
sql: {% condition date_selector %} ${date_raw} {% endcondition %} ;;
}
Third, create a yesno
dimension that captures the start and end of date_selector
and subtracts a month. The SQL will vary by dialect.
dimension: is_previous_month {
type: yesno
sql: ${date_raw} > ADD_MONTHS(-1, {% date_start date_selector %}) AND ${date_raw} < ADD_MONTHS(-1, {% date_end date_selector %}) ;;
Fourth, put these yesno's into filtered measures as desired.
measure: count_selected_month {
type: count
filters: {
field: is_selected_month
value: "yes"
}
}
measure: count_previous_month {
type: count
filters: {
field: is_previous_month
value: "yes"
}
}
This content is subject to limited support.