topic Sum Field Based on a Distinct Field in QlikView https://community.qlik.com/t5/QlikView/Sum-Field-Based-on-a-Distinct-Field/m-p/196383#M501146 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P>I am trying to get a sum in a chart that is being backed by a table with a large amount of data.</P><P>I will start with a quick description:</P><P>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.</P><P>Dimension 1: [Application Type]</P><P>Expression 1: count(DISTINCT if(STATUS = 'COMPLETE', [APPLICANT KEY]))</P><P></P><P>Expression 2: <B>SUM</B>(<B>if</B>(STATUS = 'COMPLETE', <B>[Completion Time Calculation]</B>))</P><P></P><P></P><BR /><BR /> Expression 3: (2/1) <P></P><P></P><BR /><BR /> Expression 3: (2/1)<BR /><BR /> <P></P><P>Expression 3: (2/1)</P><BR /><BR /> <P></P><BR /><BR /> <P>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?</P><P>I have tried anything I could think of to no avail. Any and all help is greatly appreciated.</P><P></P><P>Thank you,</P><P>A.</P><P>QV Version 9.00.7646.9 SR6</P></BODY></HTML> Tue, 01 Feb 2011 22:04:55 GMT 2011-02-01T22:04:55Z Sum Field Based on a Distinct Field https://community.qlik.com/t5/QlikView/Sum-Field-Based-on-a-Distinct-Field/m-p/196383#M501146 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P>I am trying to get a sum in a chart that is being backed by a table with a large amount of data.</P><P>I will start with a quick description:</P><P>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.</P><P>Dimension 1: [Application Type]</P><P>Expression 1: count(DISTINCT if(STATUS = 'COMPLETE', [APPLICANT KEY]))</P><P></P><P>Expression 2: <B>SUM</B>(<B>if</B>(STATUS = 'COMPLETE', <B>[Completion Time Calculation]</B>))</P><P></P><P></P><BR /><BR /> Expression 3: (2/1) <P></P><P></P><BR /><BR /> Expression 3: (2/1)<BR /><BR /> <P></P><P>Expression 3: (2/1)</P><BR /><BR /> <P></P><BR /><BR /> <P>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?</P><P>I have tried anything I could think of to no avail. Any and all help is greatly appreciated.</P><P></P><P>Thank you,</P><P>A.</P><P>QV Version 9.00.7646.9 SR6</P></BODY></HTML> Tue, 01 Feb 2011 22:04:55 GMT https://community.qlik.com/t5/QlikView/Sum-Field-Based-on-a-Distinct-Field/m-p/196383#M501146 2011-02-01T22:04:55Z Sum Field Based on a Distinct Field https://community.qlik.com/t5/QlikView/Sum-Field-Based-on-a-Distinct-Field/m-p/196384#M501147 <HTML><HEAD></HEAD><BODY><P></P><P></P><P>Sum</P><P></P><P></P>(aggr(DISTINCT[completion time], [app_key]))/COUNT(DISTINCT [app_key] <P>)</P><P>Aggr for the win... this is what solved my problem, in case this would help anyone else.</P><BR /><BR /> <BR /><BR /></BODY></HTML> Tue, 01 Feb 2011 23:24:58 GMT https://community.qlik.com/t5/QlikView/Sum-Field-Based-on-a-Distinct-Field/m-p/196384#M501147 2011-02-01T23:24:58Z