5 Replies Latest reply: Sep 2, 2015 10:43 AM by Liron Baram RSS

    How do I create a 'negative' filter option?

      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.