Problems in Filters SQL (date fields and category)

[Diogo]

HI, I am trying to use a filter for date in sql, My code is like this:

SELECT SUM(secoes) secoes FROM (SELECT SUM(ga_sessions) secoes, PARSE_DATE("%Y%m%d",ga_date) data
FROM projeto.dataset.tabela
[[WHERE data = {{data}}]]
GROUP BY data) table_a

I try this also:

WITH Table_a AS (SELECT SUM(ga_sessions) secoes, PARSE_DATE("%Y%m%d",ga_date) data
FROM projeto.dataset.tabela
GROUP BY data)

SELECT SUM(secoes) secoes FROM Table_a
[[WHERE data = {{data}}]]

And the date filter not appear a field to select in dash.

The other problem is with a simple question, I have a column with 37 distinct values and can not be filtered using a category field.

My version:
{
"browser-info": {
"language": "en-US",
"platform": "Linux x86_64",
"userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_242-8u242-b08-0ubuntu3~18.04-b08",
"java.vendor": "Private Build",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_242",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.242-b08",
"os.name": "Linux",
"os.version": "4.15.0-1057-aws",
"user.language": "en",
"user.timezone": "Etc/GMT+3"
},
"metabase-info": {
"databases": [
"bigquery",
"postgres",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"run-mode": "prod",
"version": {
"tag": "v0.34.1",
"date": "2020-01-13",
"branch": "release-0.34.x",
"hash": "265695c"
},
"settings": {
"report-timezone": "America/Sao_Paulo"
}
}
}

[flamber]

Hi @Diogo

When you add the date-filter to the dashboard, are you selecting “Single date”? Because you’re adding it as a “Date” (filter type) in the question variable sidebar, right?

Without further context, I’m not sure what’s going on with category field. Just note that using Field Filter, the SQL is a little different and shouldn’t contain a column:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type

[Diogo]

Hi @Flamber, I select all date types.

[flamber]

@Diogo
It sounds like you’re saying that you’re using Field Filters, but the screenshot shows you’re using simple Date filter.
If you’re using a Date filter, then you need to tell which column it should match the date to:

...
[[WHERE data = {{data}}]]

[Diogo]

@flamber I try used this form [[WHERE data = {{data}}]] but the error persist.

The date field in filter can not be selected.

[flamber]

@Diogo
Which database are you querying?
And now you’re using simple Date filter on the question, and you’re using “Single Date” filter on the dashboard?

[Diogo]

@flamber
I am querying from BigQuery.

I am using all options date filter.

[flamber]

@Diogo When using Simple filters, you’re more limited on which dashboard widgets you can connect, since Metabase has to replace the variable with code, so it wouldn’t know what to do with Range, Relative or “All Options”, when you’re hardcoding the column.

[Diogo]

@flamber What are the solution in this case? make transformation in BigQuery to get column field in format that I need?

[flamber]

@Diogo I don’t understand. If you’re using Field Filters, then you can use all the widgets on the dashboard, but if you’re using simple filters, then you’re limited to how you input the variable in your query.
Please read the documentation, since it should cover almost every aspect of how filters works with Native queries:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

[Diogo]

I already read documentation, and in other tables works, only in this that not work, I think that is occur, because my column ga_date are not a date type in BQ. In other tables date column has date type and this is not occur.
I Will try modifing my data in database.
See image example of other table.

Thanks!