1 Reply Latest reply: Jul 30, 2018 6:24 PM by Daniel Berkes RSS

    Count by Group

    Mikhail Morales

      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:


      EmployeeIDStatusStatus Number
      0001Not Fixed0


      Not Fixed0
      0002Not Fixed0


      0004Not Fixed0
      0005Not Fixed0


      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.

        • Re: Count by Group
          Daniel Berkes

          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:



          mapping LOAD


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

          LOAD Distinct



               concat(Status,'/') as everyStatus

          Resident myBigTable

          Group By EmployeeID,Status

          order by Status asc;





          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)