Dashboard filter to exclude values

I have created a topN pie chart which responds to a {{TopN}} text variable.
The chart displays topN spend per Supplier.
Now I want to be able to exclude certain suppliers in the results. I was thinking of a dashboard filter that responds to a field value, but I can’t figure out how to work with operators like ’ <> ’ or NOT IN ().

Here is the native SQL (MariaDB):

SELECT banking_analysis.Vendor AS Vendor, sum(banking_analysis_t.AmountDebit) AS sum
FROM banking_analysis_t
[[where 1=1
and Vendor not in ({{notvendor}})]]
GROUP BY banking_analysis_t.Vendor
ORDER BY sum DESC
limit {{TopN}}

Any thoughts?

Hi @white
When you using a Text variable, then it is converted to a string, which probably won’t work as NOT IN and definitely won’t work for LIMIT.
You need to use Number variable.
But if the {{notvendor}} is multiple names like pepsi,fanta,sprite, then you need to convert the string into a set:
https://stackoverflow.com/questions/16990392/mysql-select-in-clause-string-comma-delimited

Hi @flamber,
actually the IN operator should work also with strings, they just have to be quoted.
The LIMIT is not the issue (it is a number variable and works like expected: I can type any number in the dashboard filter and the chart will use that as the topN).

What I am trying to achieve is the opposite of the normal field filter. The field is Vendor, which is a dimension with about 500 different values. I want the chart to show results without certain specific vendors (no wildcard search needed). So the filter in the dashboard should have either the option to select “IS NOT” operator (but it does not), or it should parse comma separated quoted values back to the SQL so I could put “NOT IN” in the native SQL.

SQL generated should be something like:

select vendor, sum(amount) `spend`
from spend_analysis
where vendor NOT in ('VendorA','VendorB')  
group by vendor 
order by  `spend` desc
limit 10

@white

No, the SQL generated by Metabase from a Text variable is '\'VendorA\', \'VendorB\'', so MySQL just sees it as a single string instead of as a set.

If you’re trying to do the opposite to Field Filter, then use the Field Filter in a sub-select - something like this would probably work:

select sa.vendor, sum(sa.amount) `spend`
from spend_analysis AS sa
[[ where sa.vendor NOT in (SELECT DISTINCT spend_analysis.vendor FROM spend_analysis WHERE {{fieldfilter}}) ]]
group by sa.vendor
order by  `spend` desc
limit 10

Hi @flamber, thank you for this! This solution works brilliantly.