I can't use the "date" filter on the dashboard


#1

I’m having a problem using date filter inside the dashboard.
Within the question, the date variable (date type) works normally, however within the dashboard that I added this question, the date filter does not work.
My field is a timestamp in this format “Thursday, March 21, 2019 11:38 AM”.
When I add the date filter to the dashboard, the question is not compatible, so I can not select the “date” column.
What can I do to be able to filter date on my dashboard?
Ps: In the data model, this column is already set to “Date and Time”.


#2

Hi @estulano
Which version of Metabase? Which database are you connecting to?
There’s no Field Type called Date and Time in Data Model. Are you using Creation timestamp?
Is it a Native Query or Custom Query? Can you reproduce the problem with the Sample Dataset?


#3

Version database 0.30.4…
“Joiin date and time” in data type.
Is native query.

example:
select user, date
from test
WHERE resource = ‘Layout’
[[AND user, ilike {{user}}]]
[[AND cast(date as date) = {{data}}]]

remembering that the variable within the native sql for the date field usually works by filtering according to what I put in the filter.

My problem is on dashboard. When I include a date filter on the dashboard.

My question on the dashboard, which has a date filter as a variable in there, does not work the date filter on the dashboard. “Not valid” when i edit the filter


#4

@estulano
I don’t seem to be able to reproduce the problem on 0.31.2
I’ve just created a question based on your example (using H2 Sample Data) and added it to my test dashboard, and when I add a Single Date filter, then I can select the reference parameter.

SELECT "PUBLIC"."PEOPLE"."NAME" AS "NAME", "PUBLIC"."PEOPLE"."CREATED_AT" AS "CREATED_AT"
FROM "PUBLIC"."PEOPLE"
WHERE "PUBLIC"."PEOPLE"."SOURCE" = 'Google'
[[AND "PUBLIC"."PEOPLE"."NAME" ilike CONCAT('%',{{user}},'%')]] --variable type is set to Text
[[AND cast("PUBLIC"."PEOPLE"."CREATED_AT" AS date) = {{data}}]] --variable type is set to Date

#5

doesn’t work for me.
See:
Only “single date” able compatible fields when i add this filter. Functions normally. But…i want use “Relative Date” to filter.!
Another options doesn’t works. Not valid. Only functions (valid) if i use (Single Date), then de question have a valid column, but i want use “Relative Date” to filter and this don’t show the valid columns like “Single Date” shows.

How fix this?
Thanks for the help.


#6

@estulano
Then use Field Filter instead, so the last line looks like this:

[[AND {{data}}]] --variable type is set to Field Filter mapped to "PEOPLE"."CREATED_AT"

https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type