I have created a field called COUNT(Product Category) and filtered it to >=2. Trust this is what you are looking for. Or is there anything specific you were looking for?
Example.twbx 1.1 MB
VERTICAL LIST with subtotals
This can be done using a calculated field with SIZE() computed Pane Down (see screenshot below)
HORIZONTAL LIST with row totals
Seemingly this only works vertically - Pane Down - and not horizontally - Pane Across.
Vertically I used another formula which works as long as their are no row total
WINDOW_SUM(IF NOT ISNULL(SUM([Sales])) THEN 1 END) >= 2
You can also use COUNT DISTINCT if you change your data source to an extract.
I have a feeling it is possible to make a solution that also works with row totals and hope some table calculation expert will share his or her knowledge in this regard.
Thanks a lot for the prompt reply.
I was looking for something more specific actually. I want to show the names of customers who have purchased from two or more categories i.e customers who have purchased products from both Office Supplies and Furniture category (any other combination) or all three categories since this database has just 3 values under product category.
Let me give you an example (please see the image). The highlighted box is a customer name which i'm not interested in as he has bought only from Office Supplies but according to COUNT(Product Category) I get a value of 4 because it calculates for the number of products purchased.
Hope i have explained it clearly now.
I feared so. You could always use COUNTD to get it done. The method suggested by kettan is a new one for me, I would try it sometime too.
Woah! The VERTICAL list with subtotals solved my problem in seconds.
Didn't require HORIZONTAL list right now, but will make a note of this for future use.
Thanks a lot for the help, Kettan.
You are welcome
Ps. Using window functions such as Size() as filters can be a "problem" in totals.
If you add a column grand total and thereafter remove the size filter, you will notice that the grand total did not change, but stayed the same (14,931,639) with or without the size filter.
Ooops! I just tried that and yes you have been right all along about the totals. There seems to be a "problem" with it.
Fortunately, I'm not looking for grand totals or row totals now so i'm good with size() function.
If you figure out a way, please do share it.
Thanks once again!
If you have a data source that supports COUNTD, the fastest & easiest solution is to use a conditional filter on Customer Name:
This will support all totals, be able to be used in a variety of views, etc.
For the table calc solution, there are two parts to a solution:
1) Set up a Grand Total calculation that works. I used the table calc technique from http://drawingwithnumber.artisart.org/customizing-grand-totals/part-2/ and turned off mark stacking using Customizing Grand Totals in Tableau v8 – The Stacking Snag | Drawing with Numbers.
2) Set up a SIZE() calculation that takes into account the increased level of detail of the view, in this case it needed an Advanced... compute using on Product Category and Product Category (copy).
Also, I generally don't use the relative compute usings of Table (foo) and Pane (foo). This is because they are completely dependent on the pill arrangement in the view, and for situations like this one where we want specific results from the table calcs, we can get much closer to a guarantee of results when we specific a fixed addressing such as Product Category (copy).
filter for 2 or more.twbx 356.4 KB
Thanks a lot for the help, Jonathan