Hi there,
I'm trying to use a Filed Filter on a native custom SQL to be able to filter globally on my Dashboard.
Most of this dashbord have been designed by using drag & drop.
On the following SQL, it has worked for the "site" but not for other element. Weirdest part is that I have duplicated this code to match another need and "site" does not work.
Each time I get the following error :
It says basically : Something went wrong.
An error occur. You can try to refresh the page or just go to the previous page.
I'm using the Open source version,
Thanks in advance,
Paul,
WITH monthly_revenue AS
(
SELECT
strftime('%Y', mois) AS year
, strftime('%m', mois) AS month
, mois AS full_month
, SUM(ca) AS monthly_revenue
, SUM(CA_budget) AS monthly_budget
FROM compare_budget_table
WHERE 1=1
AND CAST(strftime('%Y', mois) AS integer) = {{year}}
[[ AND DATE(full_month) <= DATE({{month}})]]
[[ AND {{site}}]]
[[ AND {{type}}]]
[[ AND {{sousgroupe}}]]
GROUP BY 1,2,3
)
, cumulated_revenue AS
(
SELECT
year
, month
, monthly_revenue
, monthly_budget
, full_month
, SUM(monthly_revenue) OVER (PARTITION BY year ORDER BY month) AS cumulative_revenue
, SUM(monthly_budget) OVER (PARTITION BY year ORDER BY month) AS cumulative_budget
FROM monthly_revenue
GROUP BY 1,2,3,4,5
)
SELECT
full_month
, monthly_revenue
, monthly_budget
, cumulative_revenue - cumulative_budget as variation
FROM cumulated_revenue
