How do I create a 'negative' filter option?

[#1]

Hello everyone, newbie here

I'm new to sense, and so far it seems to be able to do what I want it to do except for this one thing, which I call a negative filter.

To clarify, I'll try to work out a simple example of what I mean (meant to illustrate, not literally what I am trying to do).

I can select all customers that have made a purchase in the past year by loading data from the CUSTOMER en TRANSACTION databases where CUSTOMER_ID is matched between the two tables, by then creating a filter that has the YEAR dimension.

This gives me a list of all customers that have made a purchase in the selected year. So far so good.

However, I now want to do a selection on all customers that have made a purchase in the past but have NOT made a purchase in this year.

IE, I want a filter that, when I select the value '2015', will give me a list of all customers that do NOT have any associated transactions in 2015.

Note that this is not the same as inverting the selection: that gives customers that DO have transactions in any year apart from 2015, but that does not preclude those same customers from also having a transaction in 2015.

In a regular SQL query, I'd do this using a WHERE NOT EXISTS clause, but I do not know how (or if) I can do it in Sense.

Any help would be greatly appreciated.

ps, I'm using load script to retrieve data via OLEDB from a SQL DB.

[lironbaram]

if i understood correctly

then after you select 2015 in the customer field you have all the customers that bought some thing in white

and all the other in gray

so you go to the customer field and click on select excluded and then you have the list of all the client who didn't bought in 2015

[#3]

Nope, that does not give the required result, because it is still an inclusive selection. See also in my post:

Note that this is not the same as inverting the selection: that gives customers that DO have transactions in any year apart from 2015, but that does not preclude those same customers from also having a transaction in 2015.

Your suggestion amounts to inverting the selction, which goves the wrong result as described above.

[lironbaram]

just to clear the situation

when you select 2015 ,

which customer appear in white , and which customers appear in gray

just rechecked it if you select 2015 , and then you select excluded in clients (assuming you see only clients that bought in 2015)

than you get only clients who didn't bought in 2015 , you'll notice that 2015 is changed to gary meaning there is no data to 2015

[#5]

Let me clarify further with an example:

Data:

CustomerID | TransactionID | TransactionYear

0001 | 9001 | 2013

0001 | 9001 | 2014

0002 | 9001 | 2015

0003 | 9001 | 2011

0003 | 9001 | 2012

0003 | 9001 | 2015

Now, how do I define a filter that lets me select '2015', and gives me a resultset of CustomerID '0001' in this example?

I'm guessing I need to LOAD a specific new table for which I do a selection with some sort of EXISTS functionality, but I do not know how to do that.

[lironbaram]

if you want to display a list with one click then you can

use a listbox with this expression

=aggr(only({<Client=e(Client)>}Client),Client)