Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Try below
Count({<JLChange = {2},EmployeeId=e({<JLChange={1}>}EmployeeId)>}DISTINCT EmployeeId)
Hi,
maybe
Count(DISTINCT Aggr(If([JL Change]='2',[Employee Id]),[Employee Id]))
Regards,
Antonio
Try below
Count({<JLChange = {2},EmployeeId=e({<JLChange={1}>}EmployeeId)>}DISTINCT EmployeeId)