Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

antoniotiman
Honored Contributor III

Re: Aggr Distinct Count

Hi Phil,

may be

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

Regards,

Antonio

Community Browser