I'm in the process of evaluating Metabase Cloud or Redash self-hosted to migrate our analytics out of Chartio, and one of the most frustrating things with Metabase is how it handles filters. The majority of our charts use date range and are not anchored to a column.
Another thing that doesn't make much sense is to have a field/filter called "Relative Date" and not let the user select a specific date in this same field/filter. Both Chartio and Redash have last month, last year, 30 days, and then pick a date in the same dropdown.
Is there any plans for Metabase to support filters in a more flexible way like Redash or Chartio?
Yeah, both issues on GH are somewhat overlapping with what I'm asking. Thank you for sharing.
Re: Filter tied to columns: A filter that isn't anchored on a column could work exactly like single variables today. There's no need to require a column to have a date range or relative date.
Another example of Metabase's filters being harder to work with than Chartio or Redash. The abstraction wraps so much SQL that you can't even use aliases:
A SQL using a date range like this:
select
pfr.created_at::date response_date,
count(*)
from
honcho.performance_feedback_answers pfa
inner join honcho.performance_feedback_responses pfr on pfr.id = pfa.performance_feedback_response_id
inner join honcho.people p on pfr.author_id = p.id
where
author_id not in ({{snippet: KYT Staff}}) and
draft = false and
{{ period_start }}
group by response_date
Compiles
select
pfr.created_at :: date response_date,
count(*)
from
honcho.performance_feedback_answers pfa
inner join honcho.performance_feedback_responses pfr on pfr.id = pfa.performance_feedback_response_id
inner join honcho.people p on pfr.author_id = p.id
where
author_id not in (
select
id
from
honcho.people
where
kyc_staff is true
)
and draft = false
and CAST("honcho"."performance_feedback_responses"."created_at" AS date) BETWEEN ? AND ? group by response_date
Which will fail for not using the same alias (pfr vs performance_feedback_responses).
Sidenote: This is also HARD to debug since there's no place to see the compiled query (without inspecting the network responses and pulling it from the JSON).
The only solution is to:
select
performance_feedback_responses.created_at::date response_date,
count(*)
from
honcho.performance_feedback_answers pfa
inner join honcho.performance_feedback_responses on performance_feedback_responses.id = pfa.performance_feedback_response_id
inner join honcho.people p on performance_feedback_responses.author_id = p.id
where
author_id not in ({{snippet: KYT Staff}}) and
draft = false and
{{ period_start }}
group by response_date
I have an example to get difference between dates in days using datarange variable.
1st. How to split dates? - Simple, subquering any table (logs, access, etc.) with daterange variable in WHERE clause.
SELECT count(*) AS count,
(SELECT MIN("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}}) AS "start_date",
(SELECT MAX("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}}) AS "end_date",
...
FROM
...
2nd, Using DATE_PART function to operate with the subqueries results
SELECT
...
DATE_PART('day', (SELECT MAX("public"."database"."crm_Date") FROM "public"."database" WHERE {{date_range}})::timestamp - (SELECT MIN("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}})::timestamp)) AS "Days in range",
....
FROM ...
WHERE ..
GROUP BY ...
With this subquery tip you can access to individual "date.start" and "date.end" data from data range variable in Metabase SQL.
on the first post:
