1 Reply Latest reply on Mar 22, 2013 12:07 AM by Jim Wahl

    Countd on field1 if field2 = x

    Tom Knapik

      I am trying to create a calculated field for a set of data that has a many to many relationship between to fields. I've searched the forums and have found ideas that come close to what I'm trying to do but doesn't quite solve my problem. I need to find the distinct number of people that have and do not have a certain activity, then calculate a percentage based on these numbers. Listed below is an example of the data I am working with and what the report needs to look like. Any help would be greatly appreciated.


      In this example I need to find the distinct number of persons that have activity a1, the number of a1 activities and the distinct number of p that do not have a1 activity. Then create a report that looks similar to the one below.





      A1 count 4.00
      Distinct P5.00
      A1 rate80.00%
      No A12.00


      Thank you in advance for any insight into this type of calculation

        • 1. Re: Countd on field1 if field2 = x
          Jim Wahl

          Hi Tom,


          If you can use an extract or database connection with COUNTD, this shouldn't be too difficult (see Accessing Count Distinct (COUNTD) | Tableau Software). I created the following calculated fields:


          For A1 count: count of activities = COUNT(activity)


          For distinct number of people with activity: [person count distinct] = COUNTD([person])   --- compute along activity


          For total number of distinct people: [person count distinct total] = TOTAL(COUNTD([person]))    --- TOTAL removes the partition by activity


          For number of people without activity = [person count distinct total] - [person count distinct]


          For activity take rate: [person count distinct] / [person count distinct total]




          See attached twbx.