Interesting one - zn doesn't work because there is no null - there's no data at all.I would suspect you could get to it via a join with a list of all product categories or custom SQL. I couldn't find a solution. There's also a chance that domain padding may work - Jonathan Drummey - any ideas?
This was fun, thanks for pinging me, Alex!
Attached are a couple of solutions that are entirely created within Tableau, using table calculation and features of Tableau's order of operations. I'm using the Superstore Sales as a data source with Customers and Sub-Category being the dimensions of interest. A caveat here: the solutions use undocumented features of table calculations so the functionality could change in future releases, this is one of those cases where table calculations can do the job and doing padding via Custom SQL or a custom query might be more useful.
Both solutions use table calculation domain completion. That's what Tableau does when you put discrete (blue) pills on Rows and Columns to lay out a crosstab or small multiples view, based on the combination of values Tableau has to create panes for visual layout. You can see this in the "domain completion layout" worksheet where there are blank spaces for combinations like Aaron Bergman/Appliances.
Those empty spaces have no data, so calcs like ZN(SUM([Sales])) that are performed within the database aren't going to return anything (even Null). The first table calculation to get around this is LOOKUP(), which returns Null if there is no data. So a calculation of ISNULL(LOOKUP(SUM([Sales]),0)) returns True wherever there is no data for a given Customer/Sub-Category combination, you can see that in the "no sales test" worksheet - compare that to the "domain completion layout" worksheet. (The default Compute Using settings work fine for this calc.)
However, we can't just filter for that calc, see the "no sales test filter fail". It's returning every customer who had no sales for any Sub-Category, and we need to choose a specific Sub-Category. So the question comes up, why not just filter for Sub-Category as well? That doesn't work either, as seen in the "no sales test filter fail2". The reason why is the order of operations - the filter is performed in the query to the database, before Tableau has done the table calculation domain completion, so there are no rows being returned for the customers who have no sales that we're interested in.
For those folks who know a bit about Tableau's order of operations, the natural next step is to use a table calculation filter, like LOOKUP(MIN([Sub-Category]),0). Table calculation filters are performed after Tableau does the domain completion and other table calculations are computed. However, this doesn't work either, as seen in the "lookup sub-category filter" worksheet. The reason is again Tableau's order of operations, the inner part of the LOOKUP is the MIN([Sub-Category]) aggregation, which is performed in the query to the database and therefore before domain completion has occurred.
What we want to do is filter for the panes that have no data, but since they have no data we can't "see" in an aggregated field the dimensional values of those panes (Customer or Sub-Category). This is where we do a little table calculation wizardry. The "Sub-Category Filter" table calculation PREVIOUS_VALUE(WINDOW_MIN(MIN([Sub-Category]), 0, IIF(FIRST()==0,LAST(),0))) (remove the ,0, IIF FIRST()==LAST(),0 optimization in version 8) with a Compute Using of Customer (so it partitions on Sub-Category) will return the value of Sub-Category to every pane in the data. You can see this in the "padding sub-category value" worksheet.
*** One possible issue is that if the Sub-Category doesn't exist at all for any customer in the data returned from the query that Tableau issues to the data source, then it won't exist here. For example, if you have "Artificial Christmas Trees" as a Sub-Category and you've set up the datasource to only show May and June data when nobody has bought any Christmas trees, Tableau can't pad them. In that case, you'd be better off doing the padding via a custom query.
Now we can put all this together. In "option 1: domain completion" the two table calcs are used as filters. This is totally dependent on Customer being on either Rows or Columns and Sub-Category being on the other Shelf to trigger domain completion. If either pill is moved to another Shelf, the domain completion won't happen.
In "option 2: at the level" I've added another table calculation, INDEX(), that has an Advanced Compute Using of Sub-Category, Customer with At the Level set to Sub-Category. Then Sub-Category can be put on the Level of Detail Shelf. This takes advantage of an undocumented feature of Tableau where domain completion is triggered by having a table calc with At the Level set to a Dimension pill that is on the Marks Card.
If other Dimensions are added to the view (for example if you want to add address information for each Customer), they will automatically be added to the partitioning and can change the results of the Sub-Category Filter calc. There are a couple of workarounds for this, the first is changing the Compute Using of the calc so that it continues to return desired results, given the possibilities I can't specify in advance what that would have to be. The other is to aggregate the additional dimensions (i.e. turning them into Measures) using MIN(), MAX(), or ATTR(). In a view like this where the level of detail is the Customer, it might be possible to use the latter workaround to get at things like address info.
Let me know if this works for you!
Hi Jonathan, this is really nice solution to track customers without sales.Thank you for sharing that with us.
Let me ask you a question, how can I count for a specific sales man? how many client didn't bought Bookcases this month or any month in the past.?