Use date field filter with relative date comparison

Hi,

I would like to combine date filed filter and relative date calculation to get this.

33

Whatever date range is chosen by the user, you’ll get the result for date_range chosen in Year-1.

Here is the syntaxe i used whithout date field filter to get the expected result :

Count CASE WHEN
to_date(“table1”.“date”) >= timestamp ‘xx’
AND to_date(“table1”.“date”) <= timestamp ‘xx’
then orders END) AS
AS ORDER YEAR N,
Count CASE WHEN
to_date(“table1”.“date”) >= ADD_MONTHS(xx, -12)
AND to_date(“table1”.“date”) <= ADD_MONTHS(xx -11)
then orders END) AS
AS ORDER YEAR N -1

=> It seems ok

When I use date filter :

Count CASE WHEN
{{DATE_RANGE}}
then orders END) AS
AS ORDER YEAR N
=> OK
Count CASE WHEN
ADD_MONTHS({{DATE_RANGE}}, -12)
then orders END) AS
AS ORDER YEAR N -1
=> i get an “invalid number of arguments” error.

Am i missing something ? Is there any workaround ?

Thanks a lot for your help

Here is my config :

{
“browser-info”: {
“language”: “fr”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.182 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “Cp1252”,
“java.runtime.name”: “Java™ SE Runtime Environment”,
“java.runtime.version”: “1.8.0_65-b17”,
“java.vendor”: “Oracle Corporation”,
“java.vendor.url”: “http://java.oracle.com/”,
“java.version”: “1.8.0_65”,
“java.vm.name”: “Java HotSpot™ 64-Bit Server VM”,
“java.vm.version”: “25.65-b01”,
“os.name”: “Windows Server 2012 R2”,
“os.version”: “6.3”,
“user.language”: “fr”,
“user.timezone”: “Europe/Paris”
},
“metabase-info”: {
“databases”: [
“h2”,
“oracle”
],
“hosting-env”: “unknown”,
“application-database”: “h2”,
“application-database-details”: {
“database”: {
“name”: “H2”,
“version”: “1.4.197 (2018-03-18)”
},
“jdbc-driver”: {
“name”: “H2 JDBC Driver”,
“version”: “1.4.197 (2018-03-18)”
}
},
“run-mode”: “prod”,
“version”: {
“tag”: “v0.37.5”,
“date”: “2021-01-05”,
“branch”: “release-x.37.x”,
“hash”: “be537ee”
},
“settings”: {
“report-timezone”: null
}
}
}

Hi @Francois_data
You would have to use Date filter (possible two) instead of Field Filters, since Field Filters generate SQL, so you cannot use it like that:
https://www.metabase.com/learn/building-analytics/sql-templates/sql-variables.html
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html

You should migrate away from H2 if you are using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
And upgrade your Java:
https://www.metabase.com/docs/latest/operations-guide/java-versions.html
Also, latest release is 0.38.0.1