-
1. Re: Filter based on count of dimension
Manas Ranjan Kar Dec 30, 2013 1:30 AM (in response to Aiswarya Sundaram)Hi,
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?
Cheers !
-
Example.twbx 1.1 MB
-
-
2. Re: Filter based on count of dimension
kettan Dec 30, 2013 2:23 AM (in response to Aiswarya Sundaram)VERTICAL LIST with subtotals
This can be done using a calculated field with SIZE() computed Pane Down (see screenshot below)
SIZE()
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.
.
-
3. Re: Filter based on count of dimension
Aiswarya Sundaram Dec 30, 2013 2:44 AM (in response to Manas Ranjan Kar)Hi Manas,
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.
-
Aiswarya
-
4. Re: Filter based on count of dimension
Manas Ranjan Kar Dec 30, 2013 3:16 AM (in response to Aiswarya Sundaram)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.
-
5. Re: Filter based on count of dimension
Aiswarya Sundaram Dec 30, 2013 3:17 AM (in response to kettan)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.
-
Aiswarya
-
6. Re: Filter based on count of dimension
kettan Dec 30, 2013 4:06 AM (in response to Aiswarya Sundaram)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.
-
7. Re: Filter based on count of dimension
Aiswarya Sundaram Dec 30, 2013 4:58 AM (in response to kettan)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!
-
8. Re: Re: Filter based on count of dimension
Jonathan DrummeyDec 30, 2013 12:09 PM (in response to Aiswarya Sundaram)
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).
Jonathan
-
filter for 2 or more.twbx 356.4 KB
-
-
9. Re: Filter based on count of dimension
Aiswarya Sundaram Dec 30, 2013 9:34 PM (in response to Jonathan Drummey)Thanks a lot for the help, Jonathan
-
10. Re: Filter based on count of dimension
Jonathan DrummeyJan 2, 2014 6:50 AM (in response to Aiswarya Sundaram)
You're welcome!
-
11. Re: Filter based on count of dimension
Tiva Brown Apr 25, 2019 9:49 AM (in response to Jonathan Drummey)That Condition Filter is exactly what I needed. I landed here because I'm still getting the hang of how to establish my criteria.
Thanks!
-
12. Re: Filter based on count of dimension
Jonathan DrummeyApr 25, 2019 3:23 PM (in response to Tiva Brown)
I’m glad it worked for you!