topic Set Analysis OR logic Issue in QlikView

Set Analysis OR logic Issue

haymarketpaul — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Set Analysis OR logic Issue

sunny_talwar — Tue, 25 Oct 2016 15:06:44 GMT

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)

Re: Set Analysis OR logic Issue

haymarketpaul — Tue, 25 Oct 2016 15:18:42 GMT

Thanks again Sunny

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

Re: Set Analysis OR logic Issue

Anonymous — Tue, 25 Oct 2016 15:19:08 GMT

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

Re: Set Analysis OR logic Issue

sunny_talwar — Tue, 25 Oct 2016 15:21:08 GMT

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