What I’m trying to achieve is having a dropdown with values like day/week/month/year for the following part of my custom SQL query:
SELECT
date_trunc({{Interval}}, created_at)
...
GROUP BY 1
I can set the variable to be text, but then I need to enter those values by hand. Would it be possible to somehow convert this to a dropdown with a set of pre-populated values?
Hi @pawel
You would have to create a table with the values and use a Field Filter on that table, joined with the query.
There’s a request open for what you’re asking for: https://github.com/metabase/metabase/issues/5245 - upvote by clicking on the first post
Lucky you. If I try to run a similar query with {{my_variable}}, it says must appear in the GROUP BY clause or be used in an aggregate function.
If I directly enter ‘myvalue’, it works.
SELECT date_trunc({{revid}}, to_timestamp(s_{{clvidientId}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss')) as cdate,
sum(s_{{vid}}.engagementDetails_{{bid}}.re_int) AS "sum"
FROM s_{{vid}}.engagementDetails_{{bid}}
WHERE ((cid = {{vid}}) [[AND t0.mid > {{mid}}]] AND (cdate BETWEEN {{startDate}} AND {{endDate}}))
GROUP BY date_trunc({{revid}}, to_timestamp(s_{{vid}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))
ORDER BY date_trunc({{revid}}, to_timestamp(s_{{vid}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))
[Vertica]VJDBC ERROR: Column “engagementDetails_2.cdate” must appear in the GROUP BY clause or be used in an aggregate function