Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mloy3
Contributor II
Contributor II

Set Intersection with exclusion

Work Unit IDTask ID
A1
A2
A3
B1
B3
B4
C2
C3
C4
D1

Hi! I been cranking my head for the past few hours in trying to get to the results but to no avail. I am trying to count the instances whereby a work unit id has been associated with Task id 1, but 2 is not associated.

In the table above I would need 2 results, 1 for B and D each. 
Tried this but didnt work:
count({<[Work Unit ID]=P({<Task ID={1}>}[Work Unit ID])>*<[Work Unit ID]=E({<Task ID={2}>}[Work Unit ID])>}Work Unit ID)

1 Solution

Accepted Solutions
Vegar
MVP
MVP

You missed out some parantheses/brackets [] and a distinct

 

=count({<[Work Unit ID]=P({<[Task ID]={1}>}[Work Unit ID])> * <[Work Unit ID]=E({<[Task ID]={2}>}[Work Unit ID])>} DISTINCT[Work Unit ID])

 

View solution in original post

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

You are missing some square brackets:

Count({
	<[Work Unit ID]=P({<[Task ID]={1}>} [Work Unit ID])>*
	<[Work Unit ID]=E({<[Task ID]={2}>} [Work Unit ID])>
    }
[Work Unit ID])

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Vegar
MVP
MVP

You missed out some parantheses/brackets [] and a distinct

 

=count({<[Work Unit ID]=P({<[Task ID]={1}>}[Work Unit ID])> * <[Work Unit ID]=E({<[Task ID]={2}>}[Work Unit ID])>} DISTINCT[Work Unit ID])

 

mloy3
Contributor II
Contributor II
Author

Hi Jontydkpi, thanks for the quick reply. Even after adding [] to all the Task ID and Work Unit ID, i find this formula to yield 0 result. 

Vegar
MVP
MVP

Try this script:

LOAD * inline [
Work Unit ID,Task ID
A,1
A,2
A,3
B,1
B,3
B,4
C,2
C,3
C,4
D,1];

 

Using the two expressions by @jonathandienst  and me will result in this table-.

image.png

mloy3
Contributor II
Contributor II
Author

Thank you for your help Jontydkpi. I finally realized why it is not working! your formula is indeed correct
mloy3
Contributor II
Contributor II
Author

Thanks for your help Vegar! This is working as intended. i realized the mistake and added the DISTINCT