Can we automatically select previous year based on user input filter value? (Community)

View the original community article here

Last tested: Jan 28, 2021
 

Yes! I believe there are other way to do, but I hope it can be useful! ( BigQuery StandardSQL)
At high level, we want to use sql_always_where parameter with Liquid Parameter to achieve this:

STEP 1
Add an unquoted type parameter field in your visualization:

view: view_name {

.

.

.

parameter: base_year {

type: unquoted

}

}

STEP2
Add a sql_always_where parameter in your Explore. In a sql_always_where parameter, we want to use Liquid parameter to define BETWEEN condition based on user input (parameter filter value):

explore: explore_name {

sql_always_where:

cast(${view_name.year_from_dimension_group} as int64) -- target field

between

({% parameter view_name.base_year %} - 1) -- filter value - 1

and {% parameter view_name.base_year %} -- filter value

;;

}