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)
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.
QV Version 9.00.7646.9 SR6