6 Replies Latest reply on Jan 16, 2019 11:11 AM by Nandhini Gopalan

    Newbie, having difficulty with COUNTD.

    Nandhini Gopalan

      Hello everyone,

       

      I am new to Tableau. I am creating a viz of value in column X vs number of rows in column X. There are about 10,000 rows in column X but #number of records shows the total taking all columns into account. I read the forum and used COUNTD[column X] as a created field to calculate the size of column X. This gives me "1" as the answer instead of 10,000. Is this something that can't be done?

      The column X has 3 values, YES, NO and NA. I am attaching a screenshot for a better understanding. Kindly help! Thank you!

        • 1. Re: Newbie, having difficulty with COUNTD.
          Ankit Bansal

          Hi Nandhini,

           

          If you do COUNTD(COLX) , you will get always 1 for each value of COLX. Not sure what you are looking, you can try COUNT(COLX) instead of COUNTD.

           

          Thanks,

          AB

          1 of 1 people found this helpful
          • 2. Re: Newbie, having difficulty with COUNTD.
            megha  gawali

            Hello Nandhini,

             

            Try this one

            { FIXED [Column name]:SUM([Number of Records])}

            1 of 1 people found this helpful
            • 3. Re: Newbie, having difficulty with COUNTD.
              Jonathan Drummey

              Hi,

               

              COUNTD() counts unique values, not records. Here's an overview of the three counting aggregations:

               

              COUNTD(X) counts the distinct (unique) values of X in the current context. With X as a dimension in the view (i.e. the values of X are setting the context for the aggregation) then COUNTD(X) is going to return 1 because there is only one unique value of X (the countd) for each X (dimension).

               

              COUNT(X) counts each non-null value of X once for each record in the current context. Another way of saying this is that it's SUM(IF NOT ISNULL([X]) THEN 1 ELSE 0 END).  With X as a dimension in the view then COUNT(X) will return the number of record for each X and then if X is Null return 0.

               

              SUM(Number of Records) counts the number of records in the current context. With X as a dimension in the view then SUM(Number of Records) will return the number of records for each X.

               

              So in this case you're going to want to use COUNT(X) or SUM(Number of Records) to get the desired results.

               

              @Megha - {FIXED [Column name]: SUM([Number of Records])} will also act as a sum, however it has two issues that I believe make is less suitable for this task. First of all it's making Tableau do extra work to resolve the LOD expression when SUM([Number of Records]) will do just fine, secondly due to Tableau's internal order of operations the FIXED LOD expression is computed before regular dimension filters are applied. This requires us to remember to change dimension filters to context filters if we want to affect the results and it's all too easy to forget, especially for new users.

               

              Regards,

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: Newbie, having difficulty with COUNTD.
                Nandhini Gopalan

                Thank you Ankit. I did this initially, but I want to get the total number of rows of only column X. COUNTD(COL X), COUNT(COL X), SUM(number of records) or SUM(COL X) all give me the total number of records in the table itself and not the number of rows in Col X that is 10,000.

                1 of 1 people found this helpful
                • 5. Re: Newbie, having difficulty with COUNTD.
                  Nandhini Gopalan

                  Thank you @Megha! It showed me the same value as the SUM though.

                  • 6. Re: Newbie, having difficulty with COUNTD.
                    Nandhini Gopalan

                    Thank you for the detailed explanation Jonathan Drummey. Sum() worked after I removed an additional data source. I dont remember the reason I added it but that was the one causing problem.