There are many use cases where you may want multiple measures included in a single query but with different date fields applied to them. For instance say we want to find the number of created opportunities and number of closed opportunities in the last 30 days. To achieve this we can created two filtered measures counting the number of opportunities, one filtering on created at date and one filtering on closed at date.
This approach allows us to compare these metrics in a single query, but it can be limiting for business users who may want to filter by different timeframes without altering field definitions or for those unfamiliar with how to do so. Instead, we can leverage templated filters in the definition of the fields to allow users to toggle the timeframe being filtered on in the workbook UI.
To implement this we need to first define a timestamp filter-only field. If we wanted different timeframes to apply to these measures, we could define two different filters to control them separately.
filters:
date_filter:
type: timestamp
Next. we can use this filter field to create a helper dimension, leveraging templated filters. We need two dimensions since each of the filtered measures should be filtering on a different date field. These fields are allowing us to pass in the value entered by a user in the UI to the corresponding date field. By hiding them, users won’t be able to query these helper fields, reducing any confusion.
created_date_flag:
sql: case when {{# demo__opportunity.date_filter.filter}} created_date {{/
demo__opportunity.date_filter.filter }} = true then true else false end
hidden: true
close_date_flag:
sql: case when {{# demo__opportunity.date_filter.filter}} close_date {{/
demo__opportunity.date_filter.filter }} = true then true else false end
hidden: true
Now, we can define our filtered measures to filter for these dimensions being true.
count_created:
aggregate_type: count
filters:
stage:
is: Closed Won
close_date_flag:
is: true
count_close:
aggregate_type: count
filters:
stage:
is: Closed Lost
created_date_flag:
is: true
This results in an easy to use timeframe filter in the workbook UI, giving business users the control to filter to the timeframe they’re interested in without having to go into the model or altering field definitions.
