Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Not applicable

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

1 Reply
Highlighted
Not applicable

Sum Field Based on a Distinct Field

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.