two conditions on a field, help in set analysis

[ioannaiogr]

Hi everyone.

I'm looking to get  a total count for each distinct ID, when their last results are type A,B,C,D but not E at the same time . So none can be type E. For example, if an id has results A, B,B, C, C, E, it won't be counted, if an id has results   A, B,B, C, C the count of ids will be 1.

I'm doing

count({<ismax_control={'True'}>*(<[result]-={'E'}>*<[result]={'A','B','C','D'}>)} distinct ID)

but it is wrong, because it still counts IDs that for their last result have a type E (even though the rest are A,B,C or D type).

Your help will be precious!

Thanks in advance.

[stevejoyce]

Try like this, which is actually excluding IDs that have an E result.

count({<ID = E({<[result]={'E'}, ismax_control={'True'}>} ID)> * <ID = P({<[result]={'A','B','C','D'}>} ID)> } distinct ID)

[Mark_Winter]

count({<ismax_control={'True'}>*<result={"*A*","*B*","*C*","*D*"}-{"*E*"}>} distinct id)

[ioannaiogr]

Hello guys, thank you for your input. 

@stevejoyce , your solution is I think the closest to what I want, but it only gives me correct results only if in the filter I select the True value for ismax_control. Idk why 😕 Any ideas?

[stevejoyce]

Maybe you need to add that ismax_control in 2nd set as well...

count({<ID = E({<[result]={'E'}, ismax_control={'True'}>} ID)> * <ID = P({<[result]={'A','B','C','D'}, ismax_control={'True'}>} ID)> } distinct ID)

[ioannaiogr]

In fact I did

count({<[ID]= E({<[result]={'E'},ismax_control={'True'}>} [ID])* P({<[result]={'A','B','C','D'},ismax_control={'True'}>} [ID])>} distinct [ID])

which is very similar to what you suggested @stevejoyce and it works !   Are these the same? Because I skipped the second ID=P(.......) and just did ID=E(........)*P(.......)

[stevejoyce]

Looks good, same thing.