Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Creator II

## 'AND' and 'OR' operators in Set Analysis

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":

IDTitleStatus 1Status 2
1Test 1N/AN/A
2Test 1WILL NOT DOAPPROVED
3Test 2AAAPPROVED

(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.

1 Solution

Accepted Solutions
MVP

I guess, you could try something like:

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

+

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

*

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

)

}Title

)

16 Replies
Creator II
Author

Anybody got a chance to look into this?

Specialist

Use "+" as OR...

Change this

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

to this

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

HTH,

John

Creator II
Author

Hi John,

Thanks for your response.

With the example I have provided above, the expression works.

Here is the new set of data

[

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, APPROVED

5, Test3, WILL NOT DO, APPROVED

6, Test3, AA, N/A

];

With this data I expect the result to be 2 (Title, "Test3" hasn't fulfilled the condition). But, using your expression gives me 3 as the output. Can you help me with this new set of data?

Thank you.

MVP

I guess, you could try something like:

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

+

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

*

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

)

}Title

)

Specialist

Hi Apoorva

With the new data set, I've come up with the following If-statement:

If(WildMatch(Aggr(Concat(Status1),Title),'*N/A*'),

If(WildMatch(Aggr(Concat(Status1),Title),'*WILL NOT DO*'),

If(WildMatch(Aggr(Concat(Status2),Title),'*APPROVED*'),Count(distinct Title)),

Count(distinct Title)))

The only downpart of it, is that it needs the dimension Title to function. (The Aggr function gives a concat string per title)

The first table is just the data set,

The second displays the result, but as mentioned, needs the Title dimension.

Specialist

Hi Tresesco

Could you give me a quick rundown on how the following works exactly:

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

Thanks!

Creator II
Author

Hi Tim,

Thanks for your response!

I am creating a KPI (just a text box) to display this result. Hence would require the expression to work without adding any dimension. But this was helpful.

Thank you.

Creator II
Author

Hi Tresesco,

This works as expected. Thanks a lot for your help!

I had never tried this kind of expression in set analysis. Would be great if you could explain that piece of code as mentioned by Tim!

Thank you.

MVP
 Title={"=count( {}Id)=count(Id)"}

Searches for those Titles for which count(Id) for Status1='N/A' : count( {<Status1={'N/A'}>}Id),is equal to count(Id), i.e. - in other word there is no other Status1 member (except for 'N/A') for that title, else the counts would not match. And that was the first check need to be done by the OP.

Community Browser