Using Field Type variable gives error when applying that filter in Dashboard

1

Hello Metabase users

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:-))

2

Can you share the SQL (or a sanitized version of it)? I’m having a hard time answering your question in the abstract.

3

Hello Sameer.

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`