topic Re: Set Analysis - Email Count Based on Date Showing as Zero in QlikView

Set Analysis - Email Count Based on Date Showing as Zero

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

QV12 SR3

I have the following set analysis which shows distinct email counts based on certain dates over the previous 2 years.

For some reason the last colmn (7) is showing zeroes instead of any counts and i'm not sure what could be causing it ? the date formats are all the same (DD/MM/YYYY).

Any ideas what could be causing the lack of any figures in the last column?

Col 2

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

Col 3

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

Col 4

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

Col 5

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

Col 6

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

Col 7

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

Re: Set Analysis - Email Count Based on Date Showing as Zero

sunny_talwar — Tue, 25 Oct 2016 09:53:33 GMT

What is the format of Event Date field in your application? Is it different format to the other date fields that you have?

Re: Set Analysis - Email Count Based on Date Showing as Zero

jyothish8807 — Tue, 25 Oct 2016 09:58:14 GMT

Try this:

=Count(DISTINCT{1<[Final 1st Party Email Opt In]={'IN'},[Email Exclusions]={'OK'},[Email Status]={'Good'},[Event Type]={Open},[Event Date]={">=$(=date(Today()-730),'DD/MM/YYYY)"}>}Email)

date(Today()-730),'DD/MM/YYYY)=26/10/2014 Also check if have any data fulfilling this condition also.

Regards

Re: Set Analysis - Email Count Based on Date Showing as Zero

haymarketpaul — Tue, 25 Oct 2016 10:01:28 GMT

Thanks Sunny - you're right the other dates are numbers and the offending date was in 'DD/MM/YYYY'

Re: Set Analysis - Email Count Based on Date Showing as Zero

haymarketpaul — Tue, 25 Oct 2016 10:01:55 GMT

Perfect - Thank you - was a date format issue after all