Hi, I’m looking for a way to use a common date range field filter variable on multiple subqueries.
Actual I have two simple date variables as you can see in this simplified example:
SELECT t1.total1, t2.total2
FROM (
SELECT SUM(field1) AS total1 FROM table1
WHERE date BETWEEN {{date_start}} AND {{date_end}}
) AS t1
LEFT JOIN (
SELECT SUM(field2) AS total2 FROM table2
WHERE date BETWEEN {{date_start}} AND {{date_end}}
) AS t2 ON t2.join = t1.join
On the dashboard I have the more advanced date range filter widget for other single table questions. I also want to connect it to this question. The goal is to use something like this:
SELECT t1.total1, t2.total2
FROM (
SELECT SUM(field1) AS total1 FROM table1
WHERE {{date_range}}
) AS t1
LEFT JOIN (
SELECT SUM(field2) AS total2 FROM table2
WHERE {{date_range}}
) AS t2 ON t2.join = t1.join
But it only works for one table because of the generated SQL:
t1.date BETWEEN %start% AND %end%
Is there any way to use the same field filter for both subqueries?