Set Analysis OR logic Issue

[haymarketpaul]

Here are 2 set analysis statements that individually try to count the number of unique emails for the previous 2 years based on a certain date...

Col 2

=Count(DISTINCT{$<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[FinalRecencyDate]={">=$(=Today()-730)"}>}Email)

Col 3

=Count(DISTINCT{$<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[Registration Date]={">=$(=Today()-730)"}>}Email)

The 4th column is a failed attempt to combine the 2 statements into one.

So for example if an email has either a Registration Date OR FinalRecencyDate within the last 2 years then it should count as 1 and show in the 4th column.

Col 4

=Count(DISTINCT{$

<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[Registration Date]={">=$(=Today()-730)"}>+

<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[FinalRecencyDate]={">=$(=Today()-730)"}>

}Email)

I am seem to be getting sporadic results - i would have thought that if either column 2 or 3 has 1 in it then column 4 should too but some are showing zero ?

Any ideas where i'm going wrong appreciated - maybe there's a better way to do it altogether?

[sunny_talwar · Accepted solution]

May be try this:

=Count(DISTINCT{$<Email =

p({<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[Registration Date]={">=$(=Today()-730)"}>}) +

p({<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[FinalRecencyDate]={">=$(=Today()-730)"}>})>}

Email)

[haymarketpaul]

Thanks again Sunny

That seems to do the trick - have got some other dates to add in too so hopefully that will crack it

[Anonymous]

Following gives you only common values

=Count(DISTINCT

{$<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[FinalRecencyDate]={">=$(=Today()-730)"}> +

<[FinalRecencyDate]={">=$(=Today()-730)"}>    Email) as column4

Following gives you conditional "1"

if(Count(DISTINCT{$<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[FinalRecencyDate]={">=$(=Today()-730)"}>}Email))>=1 or

if(Count(DISTINCT{$<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[Registration Date]={">=$(=Today()-730)"}>}Email))>=1 , 1 , 0)  as Column4

[sunny_talwar]

Follow the same p() logic and you should be fine