topic Re: Aggr Distinct Count in QlikView

topic Re: Aggr Distinct Count in QlikView https://community.qlik.com/t5/QlikView/Aggr-Distinct-Count/m-p/1369696#M616837 <HTML><HEAD></HEAD><BODY><P>Hi Phil,</P><P>may be</P><P><SPAN style="font-size: 10pt;">=</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">Count</SPAN><SPAN>({<</SPAN><SPAN style="color: #800000; font-size: 8pt;">Recruit.Vacancy_Stage</SPAN><SPAN>={'Longlisting'}>} </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">DISTINCT</SPAN><SPAN> </SPAN><SPAN style="color: #800000; font-size: 8pt;">[Vacancy Ref]</SPAN><SPAN>) </SPAN></P><P>Regards,</P><P>Antonio</P></BODY></HTML> Wed, 28 Jun 2017 07:43:10 GMT antoniotiman 2017-06-28T07:43:10Z Aggr Distinct Count https://community.qlik.com/t5/QlikView/Aggr-Distinct-Count/m-p/1369694#M616835 <HTML><HEAD></HEAD><BODY><P>I am looking for a way to show a distinct count of vacancies, however some of the vacancies have more than one applicant.  Please see the table below:</P><P></P><P>    </P><TABLE border="0" cellpadding="0" cellspacing="0" width="779"><TBODY><TR><TD class="xl65" height="17" width="243">Group</TD><TD class="xl65" style="border-left: none;" width="170">Vacancy Ref</TD><TD class="xl67" style="border-left: none;" width="170">Applicant Ref</TD><TD class="xl65" style="border-left: none;" width="98">Recruit.Vacancy_Stage</TD><TD class="xl65" style="border-left: none;" width="98">Recruit.Vacancy_State</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF1</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF1</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF1</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF2</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp2</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF2</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF3</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF3</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF4</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF3</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF5</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF3</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF6</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF3</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF7</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF3</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF8</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF3</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF9</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF3</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF10</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF4</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF11</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF5</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF12</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF6</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF13</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp3</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF7</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF14</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF8</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF15</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF9</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF16</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF10</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF17</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp2</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF11</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF18</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF12</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF19</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF13</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF20</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF13</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF21</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF14</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF22</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Closed</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF15</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF23</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR><TR><TD class="xl66" height="17" style="border-top: none;">STAFF_grp1</TD><TD class="xl66" style="border-top: none; border-left: none;">VAC.REF16</TD><TD class="xl68" style="border-top: none; border-left: none;">APP.REF24</TD><TD class="xl66" style="border-top: none; border-left: none;">Longlisting</TD><TD class="xl66" style="border-top: none; border-left: none;">Advertising</TD></TR></TBODY></TABLE><P></P><P>I want to show that there are 16 Vacancies which are at Longlisting stage. </P><P></P><P>Expected Results: 16 Vacancies in Longlisting</P><P></P><P></P><P></P><P>Regards</P><P></P><P></P><P>Phil</P></BODY></HTML> Wed, 28 Jun 2017 07:35:54 GMT https://community.qlik.com/t5/QlikView/Aggr-Distinct-Count/m-p/1369694#M616835 Anonymous 2017-06-28T07:35:54Z Re: Aggr Distinct Count https://community.qlik.com/t5/QlikView/Aggr-Distinct-Count/m-p/1369695#M616836 <HTML><HEAD></HEAD><BODY><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">Hi,</SPAN></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;"><BR /></SPAN></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">Try like below,</SPAN></P><P><SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">Expression<BR /></SPAN></P><P><STRONG>=Aggr(count(distinct [Vacancy Ref]),[Applicant Ref])</STRONG></P><P><STRONG>or<BR /></STRONG></P><P><STRONG>=Aggr(count( total <[Vacancy Ref]> distinct [Vacancy Ref]),[Applicant Ref])</STRONG></P><P><STRONG><BR /></STRONG></P><P><STRONG><IMG alt="Capture.JPG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/168378_Capture.JPG" style="height: 300px; width: 620px;" /><BR /></STRONG></P></BODY></HTML> Wed, 28 Jun 2017 07:43:06 GMT https://community.qlik.com/t5/QlikView/Aggr-Distinct-Count/m-p/1369695#M616836 devarasu07 2017-06-28T07:43:06Z Re: Aggr Distinct Count https://community.qlik.com/t5/QlikView/Aggr-Distinct-Count/m-p/1369696#M616837 <HTML><HEAD></HEAD><BODY><P>Hi Phil,</P><P>may be</P><P><SPAN style="font-size: 10pt;">=</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">Count</SPAN><SPAN>({<</SPAN><SPAN style="color: #800000; font-size: 8pt;">Recruit.Vacancy_Stage</SPAN><SPAN>={'Longlisting'}>} </SPAN><SPAN style="color: #0000ff; font-size: 8pt;">DISTINCT</SPAN><SPAN> </SPAN><SPAN style="color: #800000; font-size: 8pt;">[Vacancy Ref]</SPAN><SPAN>) </SPAN></P><P>Regards,</P><P>Antonio</P></BODY></HTML> Wed, 28 Jun 2017 07:43:10 GMT https://community.qlik.com/t5/QlikView/Aggr-Distinct-Count/m-p/1369696#M616837 antoniotiman 2017-06-28T07:43:10Z