topic Re: Aggr Count in QlikView https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340884#M515444 <HTML><HEAD></HEAD><BODY><P>May be this?</P><P><STRONG>=Sum(Aggr(If(Count(NPI) &gt;= 2, 1), FACILITY, NPI, FULL_NAME))</STRONG></P></BODY></HTML> Fri, 12 May 2017 22:27:08 GMT sunny_talwar 2017-05-12T22:27:08Z Aggr Count https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340883#M515443 <HTML><HEAD></HEAD><BODY><P>The goal is to show a count of Providers with Duplicate entries (same NPI, name, specialty) at a facility.</P><P></P><P>In the example below, in the detailed chart, I use <SPAN style="font-size: 8pt;">=</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">if</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">count</SPAN><SPAN style="font-size: 8pt;">( </SPAN><SPAN style="color: #800000; font-size: 8pt;">NPI</SPAN><SPAN style="font-size: 8pt;">)&gt;=2,</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">count</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #800000; font-size: 8pt;">NPI</SPAN><SPAN style="font-size: 8pt;">)) </SPAN>and get 262 as the numeric count of rows (as opposed to the sum of the rows). This means there are 262 providers at this facility that are duplicated.</P><P></P><P>I want a bar chart to show the total number of duplicate providers at each facility but this number is not the same. I believe I need to use an AGGR statement but I can't figure out how. The bar chat's only dimension would be facility but I still want to show the same logic.</P><P></P><P>This is not giving me what I need: <SPAN style="font-size: 8pt;">=</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">sum</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">aggr</SPAN><SPAN style="font-size: 8pt;">(</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">count</SPAN><SPAN style="font-size: 8pt;">( </SPAN><SPAN style="color: #800000; font-size: 8pt;">NPI</SPAN><SPAN style="font-size: 8pt;">)&gt;=2,</SPAN><SPAN style="color: #800000; font-size: 8pt;">FACILITY</SPAN><SPAN style="font-size: 8pt;">)) </SPAN></P><P></P><P><IMG alt="5-12-2017 1-57-04 PM.jpg" class="jive-image image-1" src="/legacyfs/online/163227_5-12-2017 1-57-04 PM.jpg" style="width: 620px; height: 180px;" /></P></BODY></HTML> Fri, 12 May 2017 21:58:47 GMT https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340883#M515443 cbaqir 2017-05-12T21:58:47Z Re: Aggr Count https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340884#M515444 <HTML><HEAD></HEAD><BODY><P>May be this?</P><P><STRONG>=Sum(Aggr(If(Count(NPI) &gt;= 2, 1), FACILITY, NPI, FULL_NAME))</STRONG></P></BODY></HTML> Fri, 12 May 2017 22:27:08 GMT https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340884#M515444 sunny_talwar 2017-05-12T22:27:08Z Re: Aggr Count https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340885#M515445 <HTML><HEAD></HEAD><BODY><P>Long time no chat, Sunny! Thanks</P></BODY></HTML> Fri, 12 May 2017 22:28:39 GMT https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340885#M515445 cbaqir 2017-05-12T22:28:39Z Re: Aggr Count https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340886#M515446 <HTML><HEAD></HEAD><BODY><P>One more question... I need a count if NPI is null by Facility.</P></BODY></HTML> Fri, 12 May 2017 22:30:27 GMT https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340886#M515446 cbaqir 2017-05-12T22:30:27Z Re: Aggr Count https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340887#M515447 <HTML><HEAD></HEAD><BODY><P>May be</P><P><STRONG>NullCount(NPI)</STRONG></P></BODY></HTML> Fri, 12 May 2017 22:33:04 GMT https://community.qlik.com/t5/QlikView/Aggr-Count/m-p/1340887#M515447 sunny_talwar 2017-05-12T22:33:04Z