1 Reply Latest reply on Nov 28, 2016 3:20 PM by Rekha Kesavan

    Count Occurances that Happen for Multiple Departments?

    Sean Norse

      Hi all,

       

      I'm looking to create a metric that shows collaboration between departments in publishing articles.  In particular, if my raw data looks as such:

       

      DepartmentPublication ID
      Molecular BiologyID-1
      Molecular BiologyID-3
      Molecular BiologyID-4
      Molecular BiologyID-5
      ImmunologyID-2
      ImmunologyID-3
      ImmunologyID-3
      ImmunologyID-5
      ImmunologyID-5
      GeneticsID-1
      GeneticsID-1
      GeneticsID-2
      GeneticsID-6
      GeneticsID-7
      GeneticsID-8
      GeneticsID-9
      GeneticsID-9

       

      I'm aiming to create a calculated field or aggregate such that:

       

      DepartmentPublication Collaboration ID Count
      Molecular Biology2
      Immunology2
      Genetics1

       

      The logic behind this is basically:

       

           If distinct [Publication ID] by department occurs in more than 1 different department

           Then [Publication Collaboration ID] = [Publication ID]

           Else null

       

      Then I put the [Department] as the row and COUNT([Publication Collaboration ID]) as column, etc.

       

      I've had trouble thinking of a method to do this in Tableau.  I'm not sure it is possible to link [Department] and [Publication ID] rows in a calculated field.  One thought I had was to select distinct Publication IDs by Department and then look for counts greater than 1, but I'm not sure if this can be done in Tableau.

       

      Thanks in advance and sorry for any confusion,

      Sean