Let's make sure we understand your specifications. Are you saying:
For any arbitrary Product in the data set, within a selected date range of Order Date -- return a "list" of specific Customers that have ordered something but have not ordered the specific Product selected?
In other words, you want to exclude any customers that have ordered nothing whatsoever, and exclude customers that have ordered the selected product among other things?
Would that be correct ?
Thanks for the response.
You seem to have understood the requirement but the sample provided doesn't produce the expected results.
Take for example the product 'Avery Binder Labels' in your sample. When this item is selected in your filter the list contains 16 Customers.
Looking at your dataset we see the following:
- There are 2858 distinct customers
- Across the entire date range 6 distinct customers ordered 'Avery Binder Labels'
- This leaves us with 2852 distinct customers that did NOT order 'Avery Binder Labels'
It is the 2852 customers that I am trying to identify.
Note: The sample set is probably not a real world example for me. I work in the B2B space were the vast majority of customers order the top products. I am trying to identify those customers who didn't order these products.
To help clarify further, it the data were stored in database the following query would give me the results I need assuming I know the filters are : 'Avery Binder Labels' and '01/07/2010' AND '30/07/2010'
SELECT DISTINCT [Customer Name]
FROM superstore A
WHERE NOT EXISTS(
SELECT [Customer Name]
FROM superstore B
WHERE B.[Customer Name] = A.[Customer Name]
AND B.Item = 'Avery Binder Labels'
AND B.[Order Date] BETWEEN '01/07/2010' AND '30/07/2010'
Thanks again for your response, if you can provide more feedback i'd be stoked.
In a nutshell. Using filters as you provided I want to work out - Of the customers that placed orders in this selected time frame who did not include the selected product on any of their orders.
Right. I had already deleted my post with the sample workbook because I knew it didn't do what you wanted.
Sorry Mate, I was so excited about the reply that I went and responded without realising you had removed it.
Awesome feedback though. Appreciated.
It depends how your final viz will work, but here's a technique (...you can find a better explanation that mine if you watch the excellent Think Data Thursday's Let's Talk About Sets webinar...definitely an hour well spent!) The specified item was not found.
The basic trick is to use a parameter and a set.
The Parameter is set up to have all the product names in (now this means it won;t update dynamically...although this feature might be on the way, and you can only select product by product)
I've then created a set, on the customer name, with the condition
MAX( IIF([Product Name] = [Didn't Puchase?], 1, 0 ) ) = 0
If puts a 1 next to all rows where Product Name = Selected Product Name from the Parameter, and then takes the Max. If this equals zero then they didn't purchase.
I've then used this as a filter, so by changing the selected product in the parameter the list of non-buying customers updates
Hope this helps (this is a very handy trick!)
Customers Who didnt purchase X.twbx 444.7 KB
Simon - i've not yet decided whether this is Genius or just Devillishly Brilliant
Either way it works a treat !!! Tis indeed a very handy trick.
Thanks for this and thanks to the others for their responses also.
...let's say it's a bit of both!! I wish I could take credit for thinking of it, but as you'll see when/if you watch the Let's Talk about Set's TDT Bethany covers a very similar example (in fact 80% of the presentation are different use cases of the same basic set conditional formula).
It's a very handy technique which can be used in so many different ways. The very handy thing with sets is also that they can be combined. So in one of the examples in the TDT, one set was created on the condition that a customer spent over X amount (set by a parameter), and another set for customers who hadn't bought anything from a given category. The intersection of these 2 sets was created (using combine sets and selecting the intersection), and this set (in) was put on the filter. The user could then see all customers who had spend more than X, but hadn't bought anything from Category Y. As you can imagine this is very handy for Customer/Store segmentation/clustering.
Cheers Simon, I will most certainly check out the Sets TDT. What you have mentioned is exactly the type of information I will be after.
Simon is there a way to do this without having to select a specific product in the parameter? I've created a similar post here and someone posted the same solution as yours: Show Attributes Of A Field That Are NOT Associated With A Customer What we're trying to figure out is how to show all of the products a customer did not purchase, not just a single product as specified by a parameter?
We tried adding the "All" value but I've found that just places all customers inside the "In" set.
Although the data source is Excel, we can still use the technique that Ray gave here Re: Show Attributes Of A Field That Are NOT Associated With A Customer
We can use Custom SQL to generate the List of All Products, and then again to get a list of all customers...and then cross-join these (as per Ray's answer) and then Left Join the original data back on. This will give us a row for every possible Product/Customer combination, where only the rows with sales against them will have a sales value. As you can probably already see, once we get to that the Tableau is very easy.
Is this is an approach that's feasible? if so let me know and I can work up an example from superstore.
I didn't even know Custom SQL for Excel from Tableau was an option!!! Feeling a little behind but also really relieved; an example would be great, thanks!
No shame in that at all...prior to Tableau 9 whenever we connected to Excel we got the customSQL option, but since T 9.0 it's been hidden away! When you connect to Excel, you need to use the Legacy Connection (hidden away in the open dialogue box)
So once we have this we can create a CustomSQL query that will create a row for every possible Customer/Product combination...Hopefully you have less products than superstore! as this generated 1.5M rows!! (not that big in Tableau TDE terms, but from a dataset of 9k rows, once rolled up to Customer/Product level that's quite an expansion!)
Here's the Custom SQL for that....
SELECT [c].[Customer Name] AS [Customer Name],
[p].[Product Name] AS [Product Name]
SELECT [Orders$].[Product Name] AS [Product Name],
1 AS [Dummy]
GROUP BY [Orders$].[Product Name]
INNER JOIN (
SELECT [Orders$].[Customer Name] AS [Customer Name],
1 AS [Dummy]
GROUP BY [Orders$].[Customer Name]
) [c] ON [p].[Dummy] = [c].[Dummy]
GROUP BY [c].[Customer Name],
So once we have this, I can then do another bit of custom (which you might not need) to get an aggregated table of all customer/product purchases (i.e. taking the Transaction level out)
SELECT [Orders$].[Customer Name] AS [Customer Name],
[Orders$].[Product Name] AS [Product Name],
SUM([Orders$].[Sales]) AS [Sales]
GROUP BY [Orders$].[Customer Name],
We can then do a LEFT JOIN (where we can just use the Tableau Join UI)
I also removed (hide) the 2nd set of Customer Name, Product Name. btw you will (most likely) need to use an Extract as 1.5 Million rows live against Excel isn't fast!!
Once we've done this the flag for bought/not bought is really simple (I've added the NOT so if someone has bought it the flag goes to true!)
[Customer Bought/Not Bought Flag]
Hope that helps
Thanks for all this Simon - apparently I can't do this on a Mac. Maybe I can find a way to do this inside Excel using Macros. Wouldn't happen to know of any alternatives, would you?