5 Replies Latest reply on Jun 20, 2012 1:09 AM by Emma Whyte

    Counting within an aggregation

    Emma Whyte

      Hello,

       

      I am working with satisfaction survey data for a client. The data includes a list of advisers and satisfaction quotes given about them by the survey respondents.

       

      I am trying to filter out advisers based on a count of the number of  surveys per adviser using the following calculation:

       

      count([Adviser])

       

      However this filter does not work when the Quotes field is included in the table. When the Quotes are added to the table the count function is counting the quotes as individual records, and not counting by the instances of each adviser appearing in the data. It is not counting within the aggregation.

       

      Can anyone give me an idea of why this is happening in Tableau and a solution to this issue? I have attached a packaged workbook with dummy data in as a guide.

       

      Thanks

       

      Emma

        • 1. Re: Counting within an aggregation
          Alex Kerin

          I think total(count([Advisor])) does what you want, with Advisor on the left side of the advanced compute, Quotes on the right. However that range goes up to 112, not 55

          1 of 1 people found this helpful
          • 2. Re: Counting within an aggregation
            Emma Whyte

            Hi Alex,

             

            Thanks for the reply, that calculation is perfect. The only problem is that I can't set it as a global filter to work across all worksheets in the workbook.

             

            Would you know a way of making it a global filter?

             

            Thanks

            • 3. Re: Counting within an aggregation
              Alex Kerin

              A table calc cannot be a global filter I don't think. I'm not sure how you can achieve this. I would imagine it could be done though.

              • 4. Re: Counting within an aggregation
                Tracy Rodgers

                I haven't tried this per se, but perhaps you could use a parameter that would reference the table calculation on each page?

                 

                -Tracy

                1 of 1 people found this helpful
                • 5. Re: Counting within an aggregation
                  Emma Whyte

                  Hi Tracy,

                   

                  The Parameter is a good suggestion but unfortunately it didn't work for me. It wouldn't let me use the field to pull in information in to the Parameter to set a range or a list etc.

                   

                  I have got around it for now by using two data sources (data connections published on Tableau server). I duplicated the data source I was already using and named it differently so Tableau thought it was a different source in the workbook. Something I have seen mentioned before on the forums about getting around removing global filters on single sheets.

                   

                  I then used a global filter on every sheet but the quotes sheet. Used the second data connection to create the quotes sheet and used a local filter using the total(count([Adviser])) calculation.

                   

                  The only negative to this is that I have a sheet at the start of the workbook where users can set all the filters for the report. Now the user has to set the minimum number of surveys per adviser twice. But it's not a huge issue.

                   

                  Thanks and any other ideas are welcomed!