6 Replies Latest reply on Oct 2, 2010 5:14 AM by Zach Leber

    equivalent of COUNTIF across different levels of aggregation?

    Zach Leber

      I have two views: Project Rollup and Sample Details.  In Sample Details there is a calculated field called Coverage that sums a measure value across multiple records for each Sample.  There is also a calculated field called Coverage Met that is set to 1 or 0 depending on whether Coverage > 3.80.  I've added a table calculation to sum Coverage Met which produces a value at the bottom of 81, which is the number of rows/samples that have their Coverage Met set to 1.  What I really want is to display this in the rolloup section at the top, which doesn't use Sample as a dimension but simply displays the number of samples for the project = COUNTD([Sample]).  Is there any way to get this value (81) there?  I am using Tableau 5.1.

        • 1. Re: equivalent of COUNTIF across different levels of aggregation?
          Joe Mako

          try:

           

          IIF([Coverage]>3.8,1,0)


          as your calculated field

           

          Does the attached give you the results you are looking for?

          • 2. Re: equivalent of COUNTIF across different levels of aggregation?
            Zach Leber

            Thanks Joe, I couldn't read your TWBX (I'm using 5.1), but I am already using exactly the IIF you suggest.  What I can't figure out is how to count the number of rows that have the calculated Coverage Met = 1.  Other than using a running table sum as shown in the picture.  But what I really want is to be able to access that calculated count in another view on the same dashboard that uses a different level of aggregation.  It has to be calculated at the sample level but I want it totaled at the project level.

            • 3. Re: equivalent of COUNTIF across different levels of aggregation?
              Joe Mako

              Are you counting or summing your "Coverage Met" calc field?

               

              Also, I took a look at your screen shot, and you have Nulls where I would have expected zeros.

               

              I attached the worksheets I made as an image, and here is a link to it in Tableau Public: http://public.tableausoftware.com/views/coverage_1/Dashboard1

              • 4. Re: equivalent of COUNTIF across different levels of aggregation?
                Zach Leber

                (repost, first response seems to have gotten lost)

                 

                Thanks for staying with me Joe, you understand what I'm looking for and I was able to reproduce what you did, but my data has one extra level of complexity which is the rub. Coverage is itself an aggregation summing multiple records of partial coverage for each sample, not an element in the database that contains total coverage per sample.  This forces Coverage Met to be an aggregation as well.  So rather than three SUMs on my measure values shelf for my details view, it contains:

                 

                  AGG(Coverage)

                  AGG(Coverage Met)

                  ^AGG(Coverage Met)

                 

                 

                And my roll-up project view measures are similarly:

                 

                  AGG(Number of Samples)

                  AGG(Coverage Met)

                 

                Where Number of Samples = COUNTD([Sample]) because there are multiple records for each sample so I can't use SUM(Number of Records).  So at the roll-up level, Coverage Met compares the aggregated total coverage for all samples with 3.8 and returns 0 or 1.

                 

                Any additional suggestions or alternatives are welcome.

                 

                The reason my picture shows blanks instead of zeroes is because I used a custom number format of # to hide zeroes so the positive values stand out in a table full of zeroes.  That's a neat trick that works because Tableau's custom number format uses Excel's custom number format of P;N;Z;T (a semicolon-delimited list of formatting strings for positive, negative, zero, and text values) so by only using a single # we only format and display positive numbers.

                • 5. Re: equivalent of COUNTIF across different levels of aggregation?
                  Joe Mako

                  What you are looking for is an aggregate of an aggregate, and my preferred way to accomplish this in Tableau is with custom SQL. For Example, if your default data connection SQL looked like:

                   

                   

                  SELECT [Sheet1$].[Coverage] AS [Coverage],
                  
                     [Sheet1$].[Project] AS [Project],
                    [Sheet1$].[Sample] AS [Sample],
                    [Sheet1$].[Sub Sample] AS [Sub Sample]
                  FROM [Sheet1$]
                  


                   

                  you could rewrite it to:

                   

                   

                  SELECT SUM([Sheet1$].[Coverage]) AS [Coverage],
                  
                     [Sheet1$].[Project] AS [Project],
                    [Sheet1$].[Sample] AS [Sample]
                  FROM [Sheet1$]
                  GROUP BY [Sheet1$].[Project],
                    [Sheet1$].[Sample]]
                  


                   

                  so your coverage starts out aggregated for Tableau.

                   

                  Above, I am guessing you are summing the coverage values for each sub sample, you would edit the first line so it gives you the coverage amount you want returned at the sample level.

                   

                  If you need any help, just post the default content of the textarea in the dialog when you edit the connection and change the option to Custom SQL.

                  • 6. Re: equivalent of COUNTIF across different levels of aggregation?
                    Zach Leber

                    Fantastic.  Following your Custom SQL example I was able to pre-aggregate my data to get the functionality I needed.  Results attached.  Thank you very much Joe.