Hi,
I would like to combine date filed filter and relative date calculation to get this.
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(TM) 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(TM) 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
}
}
}