I have seen a few similar questions here, but none had the answer, so I will ask again:
I want to apply a filter on a SQL query in a dashboard. When I filter explicitly by WITH in the query it works. However, when I add a Field Type variable it gives me an error in the dashboard.
I have asked the admins to change the field type to category and it has been working with GUI based cards.
When I use name filtering within the question, it works
When I use the date filter in the dashboard with date variable, it works as well.
Does anyone have any suggestions? Am I missing some limitation of the variable filtering?
Thanks for helping me out. Metabase has helped me a lot with simple queries, but I want to take it further:-))
I just came to write, that I have solved this issue by not using Field Type variable, but a regular text variable. When I open a dashboard, I can link a filter there and it sort of works. I just cannot use date filter, I am inputing numbers instead (eg: In the last x days…)
However here is the code of the current solution, if you know what went wrong, I would be grateful:
`SELECT (sum(“public”.“statistics”.“spend”)/nullif((sum(“public”.“statistics”.“impressions”)/1000),0)) AS “average cpm”, “campaign__via__id_campaign”.“objective” AS “objective”, (date_trunc(‘week’, (“public”.“statistics”.“date” + INTERVAL ‘1 day’)) - INTERVAL ‘1 day’) AS “date”
FROM “public”.“statistics”
LEFT JOIN “public”.“campaign” “campaign__via__id_campaign” ON “public”.“statistics”.“id_campaign” = “campaign__via__id_campaign”.“id” LEFT JOIN “public”.“client” “client__via__id_client” ON “public”.“statistics”.“id_client” = “client__via__id_client”.“id”
WHERE “client__via__id_client”.“default_country_code” = {{country}} AND (CAST(“public”.“statistics”.“date” AS date) BETWEEN CAST((NOW() + INTERVAL ‘-{{LastDays}} day’) AS date)
AND CAST((NOW() + INTERVAL ‘-1 day’) AS date))
GROUP BY “campaign__via__id_campaign”.“objective”, CAST(“public”.“statistics”.“date” AS date)
ORDER BY “campaign__via__id_campaign”.“objective” ASC, CAST(“public”.“statistics”.“date” AS date) ASC`