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.

       

      personactivity
      p1a1
      p1a1
      p1a2
      p1a3
      p2a2
      p2a4
      p2a3
      p3a1
      p4a1
      p5a4

       

       

      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]

           

          activity-person.png

           

          See attached twbx.

           

          Jim