3 Replies Latest reply: Oct 2, 2013 7:35 AM by Jim Gay RSS

    Distinct Count Total in Straight Table

    Jim Gay

      I have a question about getting the distinct count of a column in a straight table.  I'm getting closer to figuring this out but cannot seem to arrive at the right value for the total.  I want to get the distinct count where the ATTEST_FLAG = 1 and summarize this count in the # ATTESTING column. Here is what my table looks like:

       

      PROV_IDDATEATTEST_FLAG# ATTESTING
      A1/1/20131
      A2/1/20131
      B1/1/20131
      B2/1/20130
      C1/1/20130
      C2/1/20130

       

      Here is what my current expression looks like in the # ATTESTING column:

      =If(Column(28),Count(Distinct Aggr(PROV_ID,PROV_ID)))

       

      I have many columns in my table, but these are the only relevant ones.  Perhaps the sort order is playing a factor here?  Basically I have around 25 columns that all work together to see if the ATTEST_FLAG should be 1 or 0.  Once that flag is set I want to summarize the PROV_ID with a distinct count and get the total. The expression above returns a value 185, but the correct answer is 220.  This seems like a relatively simple expression but I can't seem to get this working.

       

      Any help would be great!