count No of employees that doesnt have a certin status

[Saif1]

Hello Qlikers,

I have table like :

Emp num	Status
1	A
1	A
2	A
2	B
2	C
3	A
3	C

i want to count num of Emp ID where that they dont have a certain status Like Emp 3 doenst have status  B so this is one

emp 1 doesnt have B  Or C 

my end goal is to create 3 kpi's names

(Number of employees doesnt have Status A)

(Number of employees doenst have status B)

Etc

any quick solutions ? using Set anaylsis or anything.

[BrunPierre]

This is a good place to begin Excluding values in Set Analysis

[vinieme12 · Accepted solution]

(Number of employees doesnt have Status A)

=count({<EmpNum=e({<Status={'A'}>}EmpNum)>}Distinct EmpNum)

(Number of employees doenst have status B)

=count({<EmpNum=e({<Status={'B'}>}EmpNum)>}Distinct EmpNum)

[Saif1]

@vinieme12  thank you so much this worked . but when selecting an employee it gives me the opposite

like if the set has 6 employees and one doesn't have a certain selection ( without filteration it works fine but when picking a certain employee number it gives count to the whole set) it returns 5 and i want it to return 1

any help ? 

[Saif1]

Thanks this was helpful

[vinieme12]

can explain your scenario using the above sample dataset and your selection steps 

[Saif1]

IT worked after some adjustments, 

@vinieme12  can you help me with something else ?

i have table that has the following 

Item Key	From user	to user	Action date	Status	Pending User
1	A	B	1/1/2022	sent	B
1	B	C	1/1/2022	check	C
1	C	D	1/1/2022	check	D
1	D	E	1/1/2022	check	E
1	E	F	1/1/2022	check	F
1	F	A	1/1/2022	Ok	A
1	A	B	2/1/2022	Ok	-

How can i get the latest (To user) in the latest date and the latest Status? i want to make a column that says (pending user) it is just the latest (to user) column from the latest date - and if the latest status is of then the pending user is blank but if it is something else like chekcing or correction then it should have the pending user that we took from the (to user) column  any help ??

[vinieme12]

sorry im getting lost in the explanation, 

can you show by way of example, the expected output given different scenarios

[Saif1]

Yes sure,

what I have is a large table that is linked to an appraisal table 

the original appraisal table has only Final status (Complete , back for correction , rejected) 

i have a second table Named (detailed steps) this table has the detail of how the final status (in the first table) has come to form ,

example : this is the appraisal table :

Emp Num	Item Key	Final Status	Year
1	10	Complete	2020
1	11	Complete	2021
1	12	Returned For correction	2022

The Detailed steps look like this :

Item Key	From user	To user	Action	Comments	"Wanted new column PENDING USER	Date
12	1	1's supervisor	sending for approval		1' supervisor	1/1/2022
12	1' supervisor	Manager	Checking matiral		Manager	2/1/2022
12	Manager	1	Returned for correction	Correct the spelling	1	3/1/2022
10	1	1's supervisor	sending for approval		1' supervisor	10/10/2020
10	1' supervisor	Manager	Checking matiral		Manager	11/10/2020
10	Manager	1	COMPLETE		"BLANK"	12/10/2020

What I am trying to achieve is that i want to create the "wanted column" named (pending user) this column is derived from the column (To-User), the trick is that how to make the last row (latest date) if the status was complete the pending user is "blank" (it makes sense becuase it is completed noone is pending" but with the status "returned for correction" the pending user is the latest user in the (to - user) but only the latest date with the status <> complete. 

any help ? with thanks in advance.