5 Replies Latest reply on Mar 31, 2017 6:26 AM by Jennifer Brown

    Question re: how to disregard duplicate records in source database

    Jennifer Brown

      Hello!

       

       

      I am currently working with a data set that includes multiple records for a
      course in which students enroll, and am trying to figure out how to isolate the
      hours in the course for only one instance of the course.  For example,
      assume there are 4 records for Econ 1.  Each record shows that the class
      meets for 4 hours.  However, since it is the same class, if I add hours
      for all four records, my output will show the course meeting for 16 hours
      instead of 4.  One approach to solving this would be to sum all the hours
      and divide by the number of records (e.g.,[SUM OF HOURS] / [COURSE NAME] where
      the course name appears four times.  However, [COURSE NAME] is a
      dimension, and [SUM OF HOURS] is a measure.  I cannot figure out how
      to divide a measure by the sum of distinct records within a dimension.  (I
      have in the past used COUNTD but I understand Tableau no longer supports that
      function.

       

      I have attached a packaged workbook. Relevant fields in my attempt so
      far are as follows:

       

      [C.Sum Hours by Unique Mtg Room and Time] -- calculated field as
      follows:  sum([Mtg Hrs Per Week])/ ([Unique Physical Meeting]) 
      [This is not a valid calculation because it is dividing a measure by a
      dimension.

       

      Unique Physical Meeting -- the ID assigned to each record that meets at the
      same time in the same place each week.  Each record has a number of
      meeting hours associated with it.  As
      with the example above, there could be 4 Unique Physical Meeting records that
      are exactly the same, each with total meeting hours for the class.  To get the correct meeting hours, we need to
      sum meeting hours for all 4 records, and then divide by the number of identical
      records.

       

      The relevant worksheet in the packaged workbook is entitled “Southeast.”

      Any thoughts about how to approach this problem, either based on the initial
      solution outlined above, or any other method, would be greatly appreciated.

       

      Thank you!