1 Reply Latest reply: Feb 1, 2011 6:24 PM by Andrew Sayers RSS

    Sum Field Based on a Distinct Field

      Hello,

      I am trying to get a sum in a chart that is being backed by a table with a large amount of data.

      I will start with a quick description:

      All the fields I am working with have a unique value that is being duplicated due to merging of other tables prior to its final stage.

      Dimension 1: [Application Type]

      Expression 1: count(DISTINCT if(STATUS = 'COMPLETE', [APPLICANT KEY]))

       

      Expression 2: SUM(if(STATUS = 'COMPLETE', [Completion Time Calculation]))

       

       



      Expression 3: (2/1)

       

       



      Expression 3: (2/1)

       

      Expression 3: (2/1)



       



      The issue I am running into is that since there are duplicates now in the table, even though I am working with all fields that have a single value repeated in the duplicates, the result for Expression 2 is being exaggerated immensely. Is there a way to sum the [Completion Time Calculation] based on a Distinct [APPLICANT KEY], sort of how I do above, thus eliminating duplicate completion times from being summed?

      I have tried anything I could think of to no avail. Any and all help is greatly appreciated.

       

      Thank you,

      A.

      QV Version 9.00.7646.9 SR6