I have a query with a between clause that uses two date parameters. I want to have default value for these parameters. Using coalesce doesn’t work since seems when I don’t set parameters something other than null is sent to query. This is the problematic part of my query
BETWEEN COALESCE(date({{start_date}}),subdate(current_date, interval 1 month))
AND COALESCE(date({{end_date}}),subdate(current_date, 1))
Hi @hessam
The variable is substituted with nothing - not null - which is probably why it’s failing. Which database are you using?
I don’t know your full query, but I think that you would benefit from usinging the Field Filter instead?
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type
By using “Date Filter” as the widget type, you can then set the default value as relative, while having the option to define ranges/etc and your query would look like this:
SELECT * FROM `table`
WHERE 1=1 [[AND {{date_filter}}]]
Thank you @flamber for your reply.
I am using MySql (MariaDB). This is my full query:
SELECT count(*) AS count, orders.city AS city, orders.status AS status, orders.transport_type AS transport_type,
date(orders.launched_at) AS launched_at
FROM orders
WHERE (date(orders.deleted_at) IS NULL
AND date(orders.launched_at) BETWEEN date({{start_date}}) AND date({{end_date}}))
GROUP BY orders.city, orders.status, orders.transport_type, date(orders.launched_at)
ORDER BY orders.city ASC, orders.status ASC, orders.transport_type ASC, date(orders.launched_at) ASC
What I want to achieve is first to have this query on a dashboard and second change the between clause somehow so when no value is passed to parameters some default values can be used.
@hessam
Okay, then use Field Filters. And you'll use "Date Filter" and define the "Default filter widget value". And then just add the question to a dashboard and add a filter to it. It could look like this:
