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

[apoorvasd]

Anybody got a chance to look into this?

[johnca]

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

[apoorvasd]

Hi John,

Thanks for your response.

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

Here is the new set of data

LOAD * Inline

[

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.

[tresesco · Accepted solution]

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

   )

[timpoismans]

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.

[timpoismans]

Hi Tresesco

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

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

Thanks!

[apoorvasd]

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.

[apoorvasd]

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.

[tresesco]

Title={"=count( {<Status1={'N/A'}>}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.