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

    Count subset of data

    rob place

      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.

      dataset tableau.png

      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.

       

      Thanks in advance, and let me know if you need any more info to be able to help answer.

       

      Rob

        • 1. Re: Count subset of data
          Andrew Watson

          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
            rob place

            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
              Andrew Watson

              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
                rob place

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

                 

                Rob