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
Did you mean:
Contributor II

Set Intersection with exclusion

 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

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
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])``

6 Replies
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
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])``

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.

MVP

Try this script:

``````LOAD * inline [
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-.

Contributor II
Author
Thank you for your help Jontydkpi. I finally realized why it is not working! your formula is indeed correct
Contributor II
Author
Thanks for your help Vegar! This is working as intended. i realized the mistake and added the DISTINCT
Tags
Community Browser