Metric Calculation with records having least value in a column

[#1]

Hi,

I have the following table:

Program Name	Course Number	EmployeeId	Job Level	JobLevel As of Completion	JL Change
wqa	Abc	11111	1	2	1
dwd	Def	11111	2	2	2
sdq	Def	23333	1	1	2
dqw	Abc	23333	1	1	2
cas	Abc	1121	3	4	1
das	fgr	12122	3	4	1
asd	hjh	121212	1	2	1
asd	nklsd	121212	2	3	2
asd	mskld	121212	3	4	1
asdf	erer	333333	4	5	1

JL Change : 1- Job Level Changed, 2- No Job Level Change

I have to calculate 2 metrics Job Progression Count (Number of Employees who had a job change) and No Job Progression Count(Number of Employees who did not have a job level change)

For Job Progression Count I have expression = Count({<[JL Change]={'1'}>}DISTINCT [Employee Id]) -- works fine

However, for calculating No Job Progression, I need to exclude employee Ids 11111 and 121212 since they have a JL Change of 1 along with JL Change of 2, so the expression =Count({<[JL Change]={'2'}>}DISTINCT [Employee Id]) , does not give me desired results.

Kindly suggest!

[antoniotiman]

Hi,

maybe

Count(DISTINCT Aggr(If([JL Change]='2',[Employee Id]),[Employee Id]))

Regards,

Antonio

[vinieme12 · Accepted solution]

Try below

Count({<JLChange = {2},EmployeeId=e({<JLChange={1}>}EmployeeId)>}DISTINCT EmployeeId)