I don't know if this helps, but from a SQL perspective, it sounds like you want a Cartesian product (aka cross join) combined with an anti-join (I've seen anti-joins execute 1,500 times faster than WHERE NOT EXISTS). So if you are doing some data prep outside Tableau, I'd use that, driving from Products x Customers like:
from product a, customer b
left outer join orders c
on c.customer_id = a.customer_id
c.product_id = b.product_id
where c.product_id is null
My Tableau skills aren't sharp enough to pull this off in the tool itself. I'd love to hear if it's possible.
Thanks, but unfortunately the dataset in this case is in Excel format.
Well, if you get completely stuck, you can do it in Excel:
- Make a sheet of unique products
- Make a sheet of unique customers
- Make a cartesian product of those (How to generate all combinations from two separate lists [Pivot Table Trick] | Chandoo.org - Learn Microsoft Excel Onlin…
- Create a composite key (concatenate) product ID plus customer ID on the orders.
- Create an identical composite key on the cartesian product sheet.
- do a vlookup of the composite key from the cartesian product into the orders
- filter for all not found.
This can be done using a parameter and a set. Create a parameter based on product.
Then create a set based on customer name.
Place this to the filter shelf and select>show members in the set.
Attached is the solution workbook created in v10.3
Hope this helps.
Product not purchase.twbx 1.1 MB
Very clever alternative!
If you find my answer helpful, please mark it as helpful and correct to close this thread and for benefit of others too.
Hey Tushar, is there a way to do this without selecting one item at a time using a Parameter? So if I were to list customers on the left, and In/Out of the set you created on the columns, then the text would be products in each set. So all products are considered; not just one currently selected in the parameter.
It does - perfect, thank you!
Actually I tested it out today and it shows as all customers because "In" when "All" is selected. Still the closes workaround I've found so far, though!
Will check and let you know.
Curious to know why you based this on dates?