Set Analysis combining flags across Rows

[mellerbeck]

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?

[srchilukoori · Accepted solution]

You may also try the follwoing expression,

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

[mellerbeck]

Yes! That looks much better!