Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Group | Vacancy Ref | Applicant Ref | Recruit.Vacancy_Stage | Recruit.Vacancy_State |
STAFF_grp1 | VAC.REF1 | APP.REF1 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF1 | APP.REF2 | Longlisting | Advertising |
STAFF_grp2 | VAC.REF2 | APP.REF3 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF3 | APP.REF4 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF3 | APP.REF5 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF3 | APP.REF6 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF3 | APP.REF7 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF3 | APP.REF8 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF3 | APP.REF9 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF3 | APP.REF10 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF4 | APP.REF11 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF5 | APP.REF12 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF6 | APP.REF13 | Longlisting | Advertising |
STAFF_grp3 | VAC.REF7 | APP.REF14 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF8 | APP.REF15 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF9 | APP.REF16 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF10 | APP.REF17 | Longlisting | Advertising |
STAFF_grp2 | VAC.REF11 | APP.REF18 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF12 | APP.REF19 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF13 | APP.REF20 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF13 | APP.REF21 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF14 | APP.REF22 | Longlisting | Closed |
STAFF_grp1 | VAC.REF15 | APP.REF23 | Longlisting | Advertising |
STAFF_grp1 | VAC.REF16 | APP.REF24 | Longlisting | Advertising |
I want to show that there are 16 Vacancies which are at Longlisting stage.
Expected Results: 16 Vacancies in Longlisting
Regards
Phil
Hi,
Try like below,
Expression
=Aggr(count(distinct [Vacancy Ref]),[Applicant Ref])
or
=Aggr(count( total <[Vacancy Ref]> distinct [Vacancy Ref]),[Applicant Ref])
Hi Phil,
may be
=Count({<Recruit.Vacancy_Stage={'Longlisting'}>} DISTINCT [Vacancy Ref])
Regards,
Antonio