Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

philmywallet
New Contributor III

Aggr Distinct Count

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:

   

GroupVacancy RefApplicant RefRecruit.Vacancy_StageRecruit.Vacancy_State
STAFF_grp1VAC.REF1APP.REF1LonglistingAdvertising
STAFF_grp1VAC.REF1APP.REF2LonglistingAdvertising
STAFF_grp2VAC.REF2APP.REF3LonglistingAdvertising
STAFF_grp1VAC.REF3APP.REF4LonglistingAdvertising
STAFF_grp1VAC.REF3APP.REF5LonglistingAdvertising
STAFF_grp1VAC.REF3APP.REF6LonglistingAdvertising
STAFF_grp1VAC.REF3APP.REF7LonglistingAdvertising
STAFF_grp1VAC.REF3APP.REF8LonglistingAdvertising
STAFF_grp1VAC.REF3APP.REF9LonglistingAdvertising
STAFF_grp1VAC.REF3APP.REF10LonglistingAdvertising
STAFF_grp1VAC.REF4APP.REF11LonglistingAdvertising
STAFF_grp1VAC.REF5APP.REF12LonglistingAdvertising
STAFF_grp1VAC.REF6APP.REF13LonglistingAdvertising
STAFF_grp3VAC.REF7APP.REF14LonglistingAdvertising
STAFF_grp1VAC.REF8APP.REF15LonglistingAdvertising
STAFF_grp1VAC.REF9APP.REF16LonglistingAdvertising
STAFF_grp1VAC.REF10APP.REF17LonglistingAdvertising
STAFF_grp2VAC.REF11APP.REF18LonglistingAdvertising
STAFF_grp1VAC.REF12APP.REF19LonglistingAdvertising
STAFF_grp1VAC.REF13APP.REF20LonglistingAdvertising
STAFF_grp1VAC.REF13APP.REF21LonglistingAdvertising
STAFF_grp1VAC.REF14APP.REF22LonglistingClosed
STAFF_grp1VAC.REF15APP.REF23LonglistingAdvertising
STAFF_grp1VAC.REF16APP.REF24LonglistingAdvertising

I want to show that there are 16 Vacancies which are at Longlisting stage.

Expected Results: 16 Vacancies in Longlisting

Regards

Phil

2 Replies
devarasu07
Honored Contributor II

Re: Aggr Distinct Count

Hi,


Try like below,

Expression

=Aggr(count(distinct [Vacancy Ref]),[Applicant Ref])

or

=Aggr(count( total <[Vacancy Ref]> distinct [Vacancy Ref]),[Applicant Ref])


Capture.JPG

Highlighted
antoniotiman
Honored Contributor III

Re: Aggr Distinct Count

Hi Phil,

may be

=Count({<Recruit.Vacancy_Stage={'Longlisting'}>} DISTINCT [Vacancy Ref])

Regards,

Antonio

Community Browser