One weird trick to use date dashboard filter on your custom SQL in Metabase
However I tried to write a query I would get "Invalid Field" when trying to apply the dashboard filter to our card.
After participating a high-pressure sales meeting for one of the major BI vendors I stumbled upon an open source solution that covers 99.9% of our business use cases. When I evaluated this possibility several years ago there were no open source players in the field. Much to my surprise we found Metabase! From their site:
Metabase is the easy, open source way for everyone in your company to ask questions and learn from data.
We opted to self-host and had a test instance deployed within a few hours. The time from install to first dashboard was incredibly fast. The major benefit to this is that none of our data ever leaves our internal network.
Once our initial read-only dashboards were deployed we had additional reporting requests such that the user could analyze data over a period. No problem, there is a date filter available at the dashboard level. Unfortunately, most of my reporting is done using custom SQL and I couldn't seem to get these filters to work no matter what I tried. The documentation was vague on this point and however I tried to write a query I would get "Invalid Field" when trying to apply the dashboard filter to the card.
The resolution is simple. In your question create a field filter based on the date/time field that you want to filter by, in our case "CREATED_AT".
By creating the field filter like this you allow the dashboard filters to inject their own SQL as appropriate for the type of filter you have. Allowed date/time filters currently include:
- Month and Year - Like January, 2019
- Quarter and Year - Like Q1, 2016
- Single Date - Like January 1, 2019
- Date Range - Like January 1, 2019 - January 8, 2019
- Relative Date - Like "the last 7 Days" or "this week"
- All Options (gives the dashboard user every option listed above)
Add the question you created to your dashboard, go into edit mode, and click "Add a Filter". Choose "Time" then choose the type of filter (listed above) you want to apply. In our example we used "All Options". Finally drop down the time field you want to use in each card.
Save your dashboard and enjoy your new dashboard filter. If this has been helpful please feel free to drop me a note and let me know!