12 Replies Latest reply on Apr 25, 2019 3:23 PM by Jonathan Drummey

# Filter based on count of dimension

Hi All,

Please refer to the attached workbook.

I would like to only see the names of customers who have purchased from two or more product categories. I tried using a filter with a condition (CNT(Product category)>=2) but didn't really give me the desired result.

Would appreciate if someone could help me solve this simple problem.

Regards,

Aiswarya

• ###### 1. Re: Filter based on count of dimension

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 !

• ###### 2. Re: Filter based on count of dimension

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

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

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

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

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

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

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

2 of 2 people found this helpful
• ###### 9. Re: Filter based on count of dimension

Thanks a lot for the help, Jonathan

• ###### 10. Re: Filter based on count of dimension

You're welcome!

• ###### 11. Re: Filter based on count of dimension

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

I’m glad it worked for you!