Aggr Distinct Count

[Anonymous]

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

[devarasu07]

Hi,

Try like below,

Expression

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

or

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

[antoniotiman]

Hi Phil,

may be

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

Regards,

Antonio