Count of multiple columns with or condition in set analysis

[archire2102]

Hi,

I am trying to get the count ID where fieldA and fieldB is not empty AND field1 is not empty OR field2 is not empty OR field3 is not empty with the below expression, but its not working:

=Count({$<fieldA =-{''},fieldB=-{''},field1 -={''}>+

<fieldA =-{''},fieldB =-{''},field2 -={''}>+

<fieldA =-{''},fieldB =-{''},field3 -={''}>} Distinct [ID] )

[sunny_talwar]

I am not asking for real confidential data.... all I am seeking is mocked up dummy data which is close to your real data and which can help us see the issue

[menta]

Can you split the expression and try it?

[archire2102]

Hi Sunny,

Hope this helps to understand the data.

[sunny_talwar]

This is great, all I need know is which IDs should be counted?

[sunny_talwar · Accepted solution]

Are you looking to get 13?

[archire2102]

Yes, but there are many expressions like that so its difficult to use flag for all the expressions or is it the best way?

[sunny_talwar]

For when you have nulls, flags are usually the best way... but you don't really have to use it.... try this

=Count(DISTINCT {<ID = {"=(Len(Trim(FieldA)) > 0 and Len(Trim(FieldB)) > 0 and Len(Trim(Field1)) > 0) or

    (Len(Trim(FieldA)) > 0 and Len(Trim(FieldB)) > 0 and Len(Trim(Field2)) > 0) or

    (Len(Trim(FieldA)) > 0 and Len(Trim(FieldB)) > 0 and Len(Trim(Field3)) > 0)"}>} ID)

[archire2102]

Thanks Sunny,

So the solution here is, I used IF condition in expression itself rather than Set analysis and your solution is helpful in some cases where IF condition is also not working at front end.