How can I apply the date range selected in dashboard to SQL based question

Get Help
1

Hi,

I tried to find an understandable solution, but was not successful so far

I am quite new to SQL and Metabase and would like to use the date range selected in my dashboard for the SQL query I just created.
Unfortunately, only the total number is displayed, but not the numbers that would be displayed if the selected date range from the dashboard was taken into account.

The idea is to get a conversion rate from two calculated numbers I created with questions (where the date range selected at the dashboard successfully works)

Here is the SQL Query for the conversion rate I get from the two questions I created:

select
{{#340-sum-total-valid-leads-duplicate}}/{{#339-sum-total-valid-inquiries-duplicate}}

Here are the SQL results from both questions:

340-sum-total-valid-leads-duplicate

SELECT
COUNT(*) AS count
FROM
ticket

LEFT JOIN dynamic_field_value AS Dynamic Field Value ON ticket.id = Dynamic Field Value.object_id
LEFT JOIN dynamic_field AS Dynamic Field ON Dynamic Field Value.field_id = Dynamic Field.id
WHERE
(Dynamic Field.name = 'TicketType')

AND (
(Dynamic Field Value.value_text = 'Order')

OR (`Dynamic Field Value`.`value_text` = 'Proposal')
OR (`Dynamic Field Value`.`value_text` = 'Lead')
OR (`Dynamic Field Value`.`value_text` = 'Opportunity')

)
ORDER BY
count DESC

339-sum-total-valid-inquiries-duplicate

SELECT
COUNT(*) AS count
FROM
(
SELECT
ticket.id AS id,
ticket.create_time AS create_time,
Dynamic Field Value.value_text AS Dynamic Field Value__value_text,
Dynamic Field Value.value_date AS Dynamic Field Value__value_date,
Dynamic Field.label AS Dynamic Field__label,
Dynamic Field.create_time AS Dynamic Field__create_time,
Dynamic Field.name AS Dynamic Field__name,
Dynamic Field Value.object_id AS Dynamic Field Value__object_id,
Dynamic Field Value.field_id AS Dynamic Field Value__field_id,
Dynamic Field.id AS Dynamic Field__id
FROM
ticket

LEFT JOIN dynamic_field_value AS Dynamic Field Value ON ticket.id = Dynamic Field Value.object_id
LEFT JOIN dynamic_field AS Dynamic Field ON Dynamic Field Value.field_id = Dynamic Field.id

WHERE
(Dynamic Field.name = 'TicketType')

AND (
(Dynamic Field Value.value_text = 'Inquiry')

OR (`Dynamic Field Value`.`value_text` = 'Order')
    OR (`Dynamic Field Value`.`value_text` = 'Proposal')
    OR (`Dynamic Field Value`.`value_text` = 'Lead')
    OR (`Dynamic Field Value`.`value_text` = 'Opportunity')
  )

) AS source
ORDER BY
count DESC

Thanks for your input in advance

2

Update:

for a fixed date range selected in the query I made it work - but I think I need to use field filters to get the result applied to the date range selected in the dashboard, as the selected date range in the dashboard does not affect the "start_date" and "end_date" when using the variable type "date"?

Using field filters brings me the following error, by using the SQL query below (Field filter widget type for "start_date" and "end_date": single date

"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= 1 AND 1 = 1 ) AS query2' at line 37"

SELECT
COALESCE(query1.count / NULLIF(query2.count, 0), 0) AS result
FROM
(
SELECT
COUNT() AS count
FROM
ticket
LEFT JOIN dynamic_field_value AS Dynamic Field Value ON ticket.id = Dynamic Field Value.object_id
LEFT JOIN dynamic_field AS Dynamic Field ON Dynamic Field Value.field_id = Dynamic Field.id
WHERE
(Dynamic Field.name = 'TicketType')
AND (
(Dynamic Field Value.value_text = 'Order')
OR (Dynamic Field Value.value_text = 'Proposal')
OR (Dynamic Field Value.value_text = 'Lead')
OR (Dynamic Field Value.value_text = 'Opportunity')
)
AND ticket.create_time BETWEEN {{start_date}} AND {{end_date}}
) AS query1,
(
SELECT
COUNT() AS count
FROM
ticket
LEFT JOIN dynamic_field_value AS Dynamic Field Value ON ticket.id = Dynamic Field Value.object_id
LEFT JOIN dynamic_field AS Dynamic Field ON Dynamic Field Value.field_id = Dynamic Field.id
WHERE
(Dynamic Field.name = 'TicketType')
AND (
(Dynamic Field Value.value_text = 'Inquiry')
OR (Dynamic Field Value.value_text = 'Order')
OR (Dynamic Field Value.value_text = 'Proposal')
OR (Dynamic Field Value.value_text = 'Lead')
OR (Dynamic Field Value.value_text = 'Opportunity')
)
AND ticket.create_time BETWEEN {{start_date}} AND {{end_date}}
) AS query2;

3

Hey all - any idea how to progress here?

I tried several times to get that query running by but no success. Please let me know if there is any additional info needed.