4 Replies Latest reply on Aug 2, 2016 8:50 AM by rob place

Count subset of data

Hi all,

I'm trying to do something I assume is very basic in Tableau, but I'm stuck. I have a dataset where I want to count only the values that meet a certain criteria.

I’m analyzing engagement minutes on certain companies.  I am using different dimensions that I need to control for: month & account tier.  The dataset I am working with shows every engagement, so when I count companies, I am getting every engagement that the company has. So looking at the data below, when I am counting Target Accounts in 2016-04  What I want is Tableau to add all the engagement minutes a company has and count the company only if it has above 10 minutes. (i.e. Looking at the dataset below, I would not want abbvie to be counted in april, may or june (only 5, 4, and 1 minute, respectively) but would want July counted with well over 10 minutes.

How do I set up a simple counting formula for this?

Also....different question...I would also like to create a parameter control to allow someone to use a slider to move the minimum # of minutes to count a company. So if you wanted to see all the companies with 20 minutes of engagement.

Rob

• 1. Re: Count subset of data

To get the number of mins per account per month:

{FIXED [Account Name], [Month]: SUM([Engagement Mins])}

To identify if it should be counted:

IF {FIXED [Account Name], [Month]: SUM([Engagement Mins])} > 10 THEN 1 ELSE 0 END

The tricky part is that would supply a 1 against all of the July values so isn't a simple case of summing unfortunately. I haven't tried to duplicate the data you've screenshot to test this but a table calculation should be able to fix it.

You could create an INDEX, restarting (advanced table calculation section) every month and account. Note you may need to create a combined field between month and account to act as a unique identfier of the month/account combination from which you would restart.

Alternatively you may also be able to use FIRST - although this would also need the advanced table calculation to restart every month/account combination:

IF FIRST() = 0 THEN

IF {FIXED [Account Name], [Month]: SUM([Engagement Mins])} > 10 THEN 1 ELSE 0 END

END

If that doesn't work (you may get an aggregation error) try this:

IF FIRST() = 0 THEN

SUM(IF {FIXED [Account Name], [Month]: SUM([Engagement Mins])} > 10 THEN 1 ELSE 0 END)

END

If that also doesn't work please provide a sample dataset. Note you'll also need to be using Tableau 9+ for this to work.

1 of 1 people found this helpful
• 2. Re: Count subset of data

Andrew,

This is a really good start but I'm not sure how to put it all together.  I'd be very interested in seeing what it looks like!! I attached a sample data file that has all the correct dimensions (accounts, tiers, and months) and measurements (minutes).

Thanks again!

Rob

• 3. Re: Count subset of data

Hi Rob

See attached with parameter to choose the number of minutes. I've taken the date out of the view to not worry about the table calculation. It's in version 9.3.

• 4. Re: Count subset of data

Andrew, thanks for the work! Your solution worked, I appreciate your time and expertise!!

Rob