Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Counting distinct values between two fields

Hi,

I am new to qlik sense and want to do the following:

My data is as below :

Date

Created By-Employee1

Worked By-Employee 2

1/4/2018

Abc

ZZZ

31/3/2018

Abc

XXX

5/4/2018

XXX

Abc

10/4/2018

XXX

ZZZ

5/3/2018

YYY

AbC

I want to create the following output table:

Date

Count of distinct Employee involved

Mar 2018

3

Apr 2018

3

Count of distinct employee is the distinct count of employees across the two columns:  “Created By-Employee1” and Worked By-Employee 2”

  1. i.e for Mar 2018 : It is Abc, XXX,YYY : so count is 3

Apr 2018: It is Abc,ZZZ,XXX  : so count is 3

I tried the following, but it is not working:

Count (distinct (Created By-Employee1)) + count ({< [Worked By-Employee 2]-=P ([Created By-Employee 1])>} distinct ([Worked By-Employee 2]))

Can anyone help? Is there is way to count distinct between two fields without set analysis?

1 Reply
undergrinder
Specialist II
Specialist II

Hi Neha,

you can achive this by rearrange your data:

Table:

Load

     Date,

     Employee1 as Employee

From/Resident [table];

Load

     Date,

     Employee2 as Employee

From/Resident

;

Then you can use in expression: Count(distinct Employee) with dimension of month+Year

G.