Filtering OUT data with Metabase Parameters?

I am trying to put together a query which allows me to view overall restaurant reservation metrics but then using the filter have the ability to remove certain values from the query.

The query I’ve put together is below, I tried using !=, Not in() and NOT to filter out the data but all three options give the same error: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

If anyone has any idea why this is happening and how I can work around it, I would really appreciate it.

DATE_TRUNC(‘day’, reservations.created_at + interval ‘4 hours’) as day,
SUM(reservations.covers) filter (where reservations.status not in (‘canceled’,‘denied’, ‘no_show’, ‘partially_arrived’, ‘not_confirmed’)) as Confirmed,
SUM(reservations.covers) filter (where reservations.status = ‘denied’) as Denied,
SUM(reservations.covers) filter (where reservations.status = ‘canceled’) as Canceled,
SUM(reservations.covers) filter (where reservations.status = ‘not_confirmed’) as Not_Confirmed,
SUM(reservations.covers) filter (where reservations.status = ‘no_show’) as No_Show

from reservations
join restaurants on = reservations.restaurant_id
join regions on = restaurants.region_id

restaurants.call_center = ‘TRUE’
and reservations.origin != ‘in_house’
and regions.country_code != ‘ZZ’
[[and != ‘{{Name}}’]]
[[and restaurants.relationship_type != ‘{{Relationship}}’]]
[[and reservations.origin != ‘{{Status}}’]]
[[and reservations.referrer_tag != (’{{Referrer}}’)]]
[[and != ‘{{Region}}’]]

group by day
order by day desc

limit 30

Hi @safeddeen
Which version of Metabase?
Which type of filter are you using?
The Referrer parameter is inside a parenthesis - that doesn’t seem correct?
If you’re using Field Filters, then the syntax should be:

[[and {{Name}}]]
[[and {{Relationship}}]]
[[and {{Status}}]]
[[and {{Referrer}}]]
[[and {{Region}}]]

Hey @flamber

  1. I am currently on 0.31.2
  2. I am using field filters
  3. what if I am trying to filter out data, for example I want to see how removing certain restaurants affects the result, or if I want to omit certain regions. Do filters not support != or not in() functions?

You should consider upgrading to the latest 0.32.9 - it has loads of fixes (nothing related to your question). Always backup before upgrade, so you can downgrade in case something went wrong.
Since Field Filters generate SQL, then you cannot mess around too much with them. I think your only option would be to do a sub-query, where you NOT IN that sub-query.

This works for me in both 0.33.0-preview and 0.32.7 using MS SQL Server

select CustomerName, Region
from Customer
where [[not({{country}})]]

There’s a limitation/bug with optional clauses, when used with Field Filter, so where [[not({{country}})]] is translated to where not(1=1), when nothing is selected in the filter.

Yeah I noticed when I tried to use your method I kept getting

"No results!

This may be the answer you’re looking for. If not, try removing or changing your filters to make them less specific."

Do you know of a workaround for this? I could try setting default values for all these filters that don’t really affect the results just to make it work.

You cannot set multiple default values - that’s this issue:

I don’t have a work-around for your problem. Make the filter required seems like the only option.