Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
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
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.
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)