Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
mellerbeck
Creator II
Creator II

Set Analysis combining flags across Rows

So given

 

EmployeeID, Month, Flag1, Flag2

    1001, 1, 1, 0

    1001, 1, 0, 1

    1001, 1, 0, 0

    1002, 1, 1, 0

    1002, 1, 0, 0

    1003, 1, 0, 1

    1003, 1, 0, 0

    1003, 1, 0, 0

I would like a count of the employees that have a 1 in both Flag1 and Flag2 despite it being on different rows.

Finding the count of employeeID's with a flag1 of 1 is easy using count({<Flag1 = {1}>} EmployeeID)    which gives me 2

Of course using count({<Flag1 = {1}, Flag2 = {1}>} EmployeeID) doesn't work because such a row doesn't exist.

I came up with

aggr(count({<Flag1 = {1}>} EmployeeID) + count({<Flag1 = {2}>} EmployeeID), EmployeeID)

Or

count(DISTINCT aggr(count({<Flag1 = {1}>} EmployeeID) + count({<Flag1 = {2}>} EmployeeID), EmployeeID))

How's that look, would you do something different?

1 Solution

Accepted Solutions
srchilukoori
Specialist
Specialist

You may also try the follwoing expression,

Sum( Aggr (IF(Sum({1}Flag1)>0 and Sum({1}Flag2)>0, 1, 0), EmployeeID))

View solution in original post

2 Replies
srchilukoori
Specialist
Specialist

You may also try the follwoing expression,

Sum( Aggr (IF(Sum({1}Flag1)>0 and Sum({1}Flag2)>0, 1, 0), EmployeeID))

mellerbeck
Creator II
Creator II
Author

Yes! That looks much better!