Multiple variables for datefilter in SQL query

Hello there,

I have an SQL query that is attempting to do a total count of the sum of different values in a daterange.

I can get the query working for an individual date in SQL, but am struggling when trying to get it working in metabase using the metabase variables for the date.

Also when setting the metabase variables for the date I am unable to get it so that when the dashboard is configured I can select one date range value and this will then update all the different date values in the SQL query.

Please find below the query.

Thanks for any answers in advance.

SELECT
sum(case when

(time_ln_connection_request_sent::date = date '2021-03-11') OR
(time_ln_1st_connected_message_sent::date = date '2021-03-11') OR
(time_ln_2nd_connected_message_sent::date = date '2021-03-11') OR
(time_ln_3rd_connected_message_sent::date = date '2021-03-11') OR
(time_ln_4th_connected_message_sent::date = date '2021-03-11') OR
(time_ln_5th_connected_message_sent::date = date '2021-03-11') OR
(time_ln_6th_connected_message_sent::date = date '2021-03-11') OR
(time_ln_7th_connected_message_sent::date = date '2021-03-11') OR
(time_ln_8th_connected_message_sent::date = date '2021-03-11') OR
(time_ln_9th_connected_message_sent::date = date '2021-03-11') OR
(time_ln_10th_connected_message_sent::date = date '2021-03-11') OR
(time_ln_11th_connected_message_sent::date = date '2021-03-11') OR
(time_email_1st_sent::date = date '2021-03-11') OR
(time_email_2nd_sent::date = date '2021-03-11') OR
(time_email_3rd_sent::date = date '2021-03-11') OR
(time_email_4th_sent::date = date '2021-03-11') OR
(time_email_5th_sent::date = date '2021-03-11') OR
(time_email_6th_sent::date = date '2021-03-11')OR
(time_email_8th_sent::date = date '2021-03-11') OR
(time_email_9th_sent::date = date '2021-03-11') OR
(time_email_10th_sent::date = date '2021-03-11') OR
(time_email_11th_sent::date = date '2021-03-11')

then 1 end)
FROM prospects;

Hi @karan247
I don’t quite understand what you’re trying to do, but there’s a very big difference between Field Filters, which supports multiple date-formats, but is fairly strict in usage - and simple Date filter, which only allows single-dates, but variable is more flexible.
Have a read here:
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html
https://www.metabase.com/learn/building-analytics/sql-templates/sql-variables.html

Hi Flamber,

I am trying to use a field filter (for the multiple date-formats) as a variable.

This is so that when I configure the dashboard I can select the date range once and it will accordingly update all the values for the fields below:

time_ln_connection_request_sent
time_ln_1st_connected_message_sent
time_ln_2nd_connected_message_sent
time_ln_3rd_connected_message_sent
time_ln_4th_connected_message_sent
time_ln_5th_connected_message_sent
time_ln_6th_connected_message_sent
time_ln_7th_connected_message_sent
time_ln_8th_connected_message_sent
time_ln_9th_connected_message_sent
time_ln_10th_connected_message_sent
time_ln_11th_connected_message_sent
time_email_1st_sent
time_email_2nd_sen
time_email_3rd_sent
time_email_4th_sent
time_email_5th_sent
time_email_6th_sent
time_email_8th_sent
time_email_9th_sent
time_email_10th_sent
time_email_11th_sent

Hope that helps

@karan247 Field Filters are connected to a specific table and column, so you would have to use two simple Date filters instead.

1 Like