Metabase dashboard showing no column to filter on for relative date filter

Get Help
1

Hello,
I have this query which gives me a table.

SELECT distinct T1.id, firstname as "Name", emailaddress, phone, max(T1.createdon) as "Actual Meeting Done Date"
from T1,T2 
where T2.id = T1.id and cast(activityfields -> 0 ->> 'mx_Custom_2' as TEXT)='Meeting Done - Follow Up & Closure' and {{createdon}}
group by activity_new_opportunity.id, firstname, emailaddress, phone;

I have added this question to a dashboard. I want to apply a relative date filter on 'createdon' column so that I can get the data for past month, 7 days, etc. When I add this filter in the dashboard, I see no columns to filter on from the table. The datatype of 'createdon' column is date. Why the filter is not picking up this column? Is there any specific type of column needed for this filter?

2

Hi @suraj221b
Post "Diagnostic Info" from Admin > Troubleshooting.
If the {{createdon}} variable is a Field Filter, then you will see options to connect it on the dashboard to the SQL question.

3

Hello @flamber, please find the diagnostic information:
{
"browser-info": {
"language": "en-US",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.16.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.16.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.16.1+1",
"os.name": "Linux",
"os.version": "5.4.209-116.363.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"postgres",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "14.5"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.4.1"
}
},
"run-mode": "prod",
"version": {
"date": "2022-09-02",
"tag": "v0.44.3",
"branch": "release-x.44.x",
"hash": "7d50282"
},
"settings": {
"report-timezone": null
}
}
}

4

The {{createdon}} variable is a field filter but I'm not seeing any options to connect it to the dashboard.

Screenshot 2022-11-09 at 4.02.34 PM
Screenshot 2022-11-09 at 4.02.34 PM2548×1312 162 KB

5

Hello @flamber, any update on this?

6

@suraj221b You are either not using a Field Filter for {{createdon}}, or you are using nested querying, which does not support pass-thru of variables.

Have a look here: https://www.metabase.com/learn/sql-questions/field-filters

7

select distinct relatedprospectid, to_char(createdon, 'YYYY-MM') as "Created On"
from activity_new_opportunity
where {{createdon}}
group by "Created On", relatedprospectid

This is my query, what can I change to apply date filter in the dashboard on the createdon column? As you can see, this is not a nested query and I am using a field filter which is mapped to the date column.

8

@suraj221b Post the question metadata by going to /api/card/123, where 123 is the question ID.

9

Please find this.
{"description":null,"archived":false,"collection_position":null,"table_id":null,"result_metadata":[{"display_name":"relatedprospectid","field_ref":["field","relatedprospectid",{"base-type":"type/Text"}],"name":"relatedprospectid","base_type":"type/Text","effective_type":"type/Text","semantic_type":null,"fingerprint":{"global":{"distinct-count":1484,"nil%":0.0},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":36.0}}}},{"display_name":"Created On","field_ref":["field","Created On",{"base-type":"type/Text"}],"name":"Created On","base_type":"type/Text","effective_type":"type/Text","semantic_type":null,"fingerprint":{"global":{"distinct-count":9,"nil%":0.0},"type":{"type/Text":{"percent-json":0.0,"percent-url":0.0,"percent-email":0.0,"percent-state":0.0,"average-length":7.0}}}}],"creator":{"email":"sahil.doshi@iauro.com","first_name":"Sahil","last_login":"2022-11-10T11:28:05.813798Z","is_qbnewb":false,"is_superuser":true,"id":1,"last_name":"Doshi","date_joined":"2022-09-01T15:20:00.226195Z","common_name":"Sahil Doshi"},"can_write":true,"database_id":2,"enable_embedding":false,"collection_id":3,"query_type":"native","name":"test","last_query_start":"2022-11-10T12:29:41.598519Z","dashboard_count":0,"average_query_time":23905.666666666667,"creator_id":1,"moderation_reviews":[],"updated_at":"2022-11-10T12:32:05.449305Z","made_public_by_id":null,"embedding_params":null,"cache_ttl":null,"dataset_query":{"type":"native","native":{"query":"select distinct relatedprospectid, to_char(createdon, 'YYYY-MM') as "Created On"\nfrom activity_new_opportunity\nwhere {{createdon}}\ngroup by "Created On", relatedprospectid","template-tags":{"createdon":{"id":"e18ee0c7-8d7b-8635-8bf5-7969faac5447","name":"createdon","display-name":"Createdon","type":"dimension","dimension":["field",2069,null],"widget-type":"string/=","default":null}}},"database":2},"id":96,"parameter_mappings":[],"display":"table","entity_id":"g95BjtzOAf1_3cYPpw9hb","collection_preview":true,"last-edit-info":{"id":1,"email":"sahil.doshi@iauro.com","first_name":"Sahil","last_name":"Doshi","timestamp":"2022-11-10T12:31:12.875425Z"},"visualization_settings":{"table.pivot_column":"Created On","table.cell_column":"relatedprospectid"},"collection":{"authority_level":null,"description":null,"archived":false,"slug":"leadsquared","color":"#509EE3","name":"LeadSquared","personal_owner_id":null,"id":3,"entity_id":"5iqYUamS9mTaksIeig-LD","location":"/1/2/","namespace":null},"parameters":[{"id":"e18ee0c7-8d7b-8635-8bf5-7969faac5447","type":"string/=","target":["dimension",["template-tag","createdon"]],"name":"Createdon","slug":"createdon"}],"dataset":false,"created_at":"2022-11-10T12:25:58.583383Z","public_uuid":null}

10

@suraj221b createdon is referencing field ID 2069, which is seen as a string, not as a timestamp. So either change the dashboard filter to a Text Dropdown or fix the field.