'AND' and 'OR' operators in Set Analysis

[apoorvasd]

Hello Everyone,

Need help in set analysis expression.

I have a table which looks like below. I need to get the count of "Title" where the following conditions satisfy for "Title":

ID	Title	Status 1	Status 2
1	Test 1	N/A	N/A
2	Test 1	WILL NOT DO	APPROVED
3	Test 2	AA	APPROVED

(Status 1 is "N/A") or (Status 1 is "WILL NOT DO" and Status 2 is "APPROVED").

That means, if the "Title" has Status 1 as "N/A" and there are no other rows with the same "Title", then this should be counted as 1. And therefore my result is 1.

If there are more than one row with same "Title",  then two things apply here:

1. the query has to check if Status 1 is "N/A"  for all rows. If Yes, then count this as 1. If No, then do the below

2. If few rows have Status 1 as "N/A"  and few have Status 1 as "WILL NOT DO", then the query should check if Status 2 is "APPROVED" for rows which have Status 1 as "WILL NOT DO". If all these conditions satisfy then count this as 1 for that particular "Title", else don't count.

In my example above, the expression should return 1 as the result .

I tried the following expression, but this isn't giving me the right results. Or can I achieve this using "If" statement?

Count({$<[Status 1]={"N/A"}> , [Status 1]={"WILL NOT DO"}> * <[Status 2]={"APPROVED"}>} DISTINCT Title)

Any help on this?

Thank you.

[timpoismans]

Thanks for the info!

[timpoismans]

Thanks to Tresesco's explanation, I stumbled upon a possible issue.

Consider the following scenario tresesco‌ apoorvasd‌

Id, Title, Status1, Status2

1, Test1, N/A, N/A

2, Test1, WILL NOT DO, APPROVED

3, Test2, N/A, N/A

4, Test2, WILL NOT DO, N/A

5, Test2, N/A, APPROVED

6, Test3, WILL NOT DO, APPROVED

7, Test3, AA, N/A

Your result should be 1 in this case.

Test1 is OK.

Test2 is not OK. (Has N/A and WILL NOT DO, but no APPROVED on the same row)

Test3 is not OK. (Has no N/A as Status1)

Through Tresesco's measure, your result is 2 as he checks for the count of WILL NOT DO and APPROVED.

His measure will let Test2 through while it shouldn't.

I don't know if this is a possible scenario, but just thought I'd raise the issue.

Regards,

Tim P.

[tresesco]

Thanks Tim. You are right. This is why we need to test with more such possible scenarios. The check has to be there at the Id level instead. A small correction should work I guess, like:

=Count(DISTINCT {<Title={"=count( {<Status1={'N/A'}>}Id)=count(Id)"}>

                +

                (      <Title={"=count( {<Status1={'N/A'}>}Id)>0"}>

                    *

                      <Id={"=count( {<Status1={'WILL NOT DO'}>}Id)=count({<Status2={'APPROVED'}>}Id)"}>

                )

          }Title

  )

However, I might have to wait till you come up with another glitch for final smiley.

[timpoismans]

Hehe, just trying to help.

I think it should work with the correction.

The smiley is final

[apoorvasd]

Thanks for the explanation!

[apoorvasd]

Hi Tim,

Thanks for coming up with this possibility. This was also the case in my data set. Hence altered my expression as suggested by Tresesco.

Thank you both!

[apoorvasd]

Thank you.