Set Analysis combining flags across Rows

mellerbeck — Wed, 22 Apr 2015 16:26:56 GMT

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

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)

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

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

Re: Set Analysis combining flags across Rows

srchilukoori — Wed, 22 Apr 2015 16:55:27 GMT

You may also try the follwoing expression,

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

mellerbeck — Wed, 22 Apr 2015 16:58:42 GMT

Yes! That looks much better!