6 Replies Latest reply: May 6, 2017 3:22 AM by Gysbert Wassenaar RSS

    Count distinct?

    Susan Larsen


      I want to count all occurrences that have a date in my [unique date field] column, but if there is more than one entry the occurs on the same date , only count those that have a distinct [Claim Number].

       

      So with the below example data, the answer should be 6.

      7 rows have dates, but there are only 6 with unique claim numbers.

       

      This is the expression I started with:  count([Unique Date Field])

      But this expression returns the value of 7. 

       

      What do I need to modify in my expression to have it count as intended?

       

      Example data:

       

      Unique Date Field Claim Number
      5/1/2017 69
      5/1/2017 69
      5/1/2017 17
      5/3/2017 54
      5/3/2017 40
      5/3/2017 99
      5/3/2017 77

       

       

       

        • Re: Count distinct?
          Gysbert Wassenaar

          Add the DISTINCT qualifier and count the values of the Claim Number field: count( DISTINCT [Claim Number])

            • Re: Count distinct?
              Michael Solomovich

              I understood the question a bit differently - " if there is more than one entry the occurs on the same date " tells me that if the same 69 occurs on the 5/3/2017, it should be counted as a separate count.  If I'm right (?), the expression will be:

               

              sum(aggr(count(distinct [Claim Number]), [Unique Date Field]))

            • Re: Count distinct?
              Susan Larsen

              No success yet.

               

              Gysbert, When I replace my expression with count( DISTINCT [Claim Number]), the result provides null responses rather than a count.  Did you mean that I should be adding your statement to my existing expression of count([Unique Date Field])?  If yes, how should the combined expression statement read?

               

              Michael, When I replace my expression with sum(aggr(count(distinct [Claim Number]), [Date of EQR])) , the result is a count of 8, rather than the 7 expected.  I'm not sure why.  There are only 6 distinct claim numbers that have a [Unique Date Field] entry in my data.  There are many claim numbers in my data, but only 7 have content in the [Unique Date Field], so that's what I'm trying to have my chart rule out the duplicates in that count.

               

              Any other ideas?