10 Replies Latest reply on Oct 28, 2013 1:01 AM by Mattias Elm

# How do I filter on aggregated number of records for a specific category

I am a novice to Tableau, and I've been searching the Internets for a soulution to this problem. I can't find it though.

I have a matrix with four fields set up. The measure in the matrix is a count of "Respondents". This matrix is replicated for a number of Suppliers. Each supplier has its own row in the matrix. A visualization might look like this:

A           B

Supplier 1      C          2            30

D         5             21

A           B

Supplier 2      C          2            1

D         5             2

A           B

Supplier 3      C          2            30

D          5           21

And so on.

I want to create a filter that will exclude all suppliers where the total amount of records in the entire matrix for that particular supplier is below 30. That is, I only want to show the suppliers that have a total number of records 30 or above. In the example above, Supplier 1 and 3 should show, but not Supplier 2.

I can't figure out how to do a calculated field that will count records PER supplier. If I could do that, then I could use that calculated field as a filter.

What I'm after is similar to doing a Group Count on a specific category in Crystal Reports.

I suppose thet calculated field would look something like this:

COUNT([Respondents]) //PER, FOR EACH// [Supplier]

I'm looking for the operator to use where I put //PER, FOR EACH// above.

Sadly I can't make the data available, since it is of a sensitive nature.

Any help would be much appreciated. This can't be hard to do, I just can't find the solution.

• ###### 1. Re: How do I filter on aggregated number of records for a specific category

Sadly I can't make the data available, since it is of a sensitive nature.

But you could take that sample data table you posted, put it into Excel and post that. Do that and you're much more likely to get help.

--Shawn

• ###### 2. Re: How do I filter on aggregated number of records for a specific category

This can be accomplished in Tableau.  How about mocking up some sample data in Excel, plugging that into Tableau, and posting the packaged workbook here?  Then, someone can show you how to use Table Calcs to get at the solution you are looking for.

• ###### 3. Re: How do I filter on aggregated number of records for a specific category

Shawn Wallwork wrote:

Sadly I can't make the data available, since it is of a sensitive nature.

But you could take that sample data table you posted, put it into Excel and post that. Do that and you're much more likely to get help.

--Shawn

Great minds think alike, eh?

• ###### 4. Re: How do I filter on aggregated number of records for a specific category

You are of course correct. Here is a sample Workbook, and a sample data set.

Greatful for any help!

Dropbox - Shared

• ###### 5. Re: How do I filter on aggregated number of records for a specific category

Best to post a packaged workbook (.twbx) (Save As >> Packaged Workbook file) so we don't have to put the two together (the workbook will then be posted with data embedded).  You can post a file here, by going to "Use Advanced Editor" and choosing "Attach" at the bottom right side of this window.

• ###### 6. Re: How do I filter on aggregated number of records for a specific category

Done! It is in the same folder!

• ###### 7. Re: Re: How do I filter on aggregated number of records for a specific category

How about the attached?  it used a WINDOW_SUM(Count([Respondent ID[)) with proper compute using settings, and then a separate filter to remove results < 30. I left the window count in the view, but unchecked the option to "Show header" so you can see the results if you show that header again.

The Table Calc settings for both the window_sum and the filter are as follows:

• ###### 8. Re: Re: How do I filter on aggregated number of records for a specific category

Thank  you. I will try it out on Monday when I'm back at the office. Have a nice weekend!

• ###### 9. Re: Re: How do I filter on aggregated number of records for a specific category

Should work for any view--you just need to get the fields on the left/right side of the Advanced table calc settings correct--and that setup is dependent on how your dimensions are laid out in the view.  So, if you find you cannot get it to work with your actual workbook, post a screenshot and we'll help you set up the Table Calc settings, as needed.

Make sure to mark the question answered if this resolves your issue--this helps keep the forum "clean".  Thanks!

• ###### 10. Re: How do I filter on aggregated number of records for a specific category

Works like a charm!

Thank you for your swift and competent assistance. I would never have managed that without your help. Much appreciated!