Sum Field Based on a Distinct Field

[#1]

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

[#2]

Sum

(aggr(DISTINCT[completion time], [app_key]))/COUNT(DISTINCT [app_key]

)

Aggr for the win... this is what solved my problem, in case this would help anyone else.