Count by Group

[drunk8gods]

Hi all,

New to Qlik Sense, so I apologize if this is a simple question in advance.

Here is a sample of the data I'm working with:

EmployeeID	Status	Status Number
0001	Not Fixed	0
0001	Not Fixed	0
0002	Not Fixed	0
0002	Fixed	1
0003	Fixed	1
0004	Not Fixed	0
0004	Fixed	1
0005	Not Fixed	0

The goal is to be able to do a count of Status = Not Fixed by EmployeeID.  But if there is an entry where an EmployeeID has a status of Fixed, the Not Fixed should be excluded in the count.  Essentially we want a count of EmployeeIDs where ONLY Not Fixed exists.

So, for instance, In the dataset above, the KPI count of Not Fixed would be 2. EmployeeID 0001 has 2 entries of Not Fixed, so it should be counted as 1. EmployeeID 0002 and 0004 have a Fixed and Not Fixed so they're excluded in the KPI Count.  Employee 0005 only has Not Fixed so it is included.

The status number is just an if(Status = 'Fixed',1,0)

I'm drawing a blank as to how to do this distinct count.  Is this something that can be created as a dimension or should a new dimension be created in the load?  Any help is appreciated.

[dberkesacn]

Hi since this info is staticI would create a flag at Back End after would use set analysis.

I Did not teste it but you can understand the logic and adjust it.

Back end:

map_FinalStatus:

mapping LOAD

     EmployeeID,

     if(WildMatch(everySatus,'*Not Fixed*') ,0,1) as finalStatus;

LOAD Distinct

     EmployeeID,

     Status,

     concat(Status,'/') as everyStatus

Resident myBigTable

Group By EmployeeID,Status

order by Status asc;

temp_myBigTable:

NoConcatenate

LOAD *,

applymap('map_FinalStatus',EmployeeID) as flag_finalStatus

Resident myBigTable;

drop Table myBigTable;

RENAME Table temp_myBigTable to myBigTable;

front end:

count(distinct {<flag_finalStatus={1}>}EmployeeID)