topic Displaying count of blank Emails in QlikView

Displaying count of blank Emails

haymarketpaul — Thu, 12 May 2011 16:04:29 GMT

I was wondering if anyway could suggest the best way to display this...

I have a list of email addresses (see below). Anyone who does not have an email address has <Not Answered> as their email address.

I need to be able to display the total number of people with an email address and also the total number of people who do NOT have an email address (i.e. <Not Answered>)

Any ideas how best to go about calculating and displaying this would be greatly appreciated.

Displaying count of blank Emails

Thu, 12 May 2011 16:09:16 GMT

you should flag them when you load your data .

Exemple

In the script

if (Email = '<NotAnswered>' , 1 , 0 ) as NotAnswered,

if (Email<>'<NotAnswered>', 1 , 0 ) as Answered

Then in your graphs , you sum the two values.

Philippe

Re: Displaying count of blank Emails

haymarketpaul — Fri, 13 May 2011 11:52:13 GMT

Thanks for the reply.

That does help me isolate and total them up. In fact i adopted your idea here as i was forcing in the <Not Answered> to make it selectable

Customer:
LOAD CustomerID, 
     If(Len(Trim([Email Address])) = 0, 1, 0) as NoEmail,
     If(Len(Trim([Email Address])) <> 0, 1, 0) as EmailExists,
     If(Len(Trim([Email Address])) = 0, '<Not Answered>', [Email Address]) as Email
     FROM
CDSLayout.xls
(biff, embedded labels, table is [acs layout$]);

So i can now show the totals in a Statistics box but i would like them to be able to select one of the totals so they can filter the data down to show all the people with or without an email address.

Sadly a statistic box does not appear to be selectable

Any ideas?

Re: Displaying count of blank Emails

erichshiino — Fri, 13 May 2011 12:58:00 GMT

HI, Paul

If you want to use it to make selection you could merge the fields EmailExists and NoExists in a single one

Customer:
LOAD CustomerID,
     If(Len(Trim([Email Address])) = 0, 1, 0) as NoEmail,
     If(Len(Trim([Email Address])) <> 0, 1, 0) as EmailExists,
     If(Len(Trim([Email Address])) <> 0, 'Exists', 'NoEmail') as Existence,

     If(Len(Trim([Email Address])) = 0, '<Not Answered>', [Email Address]) as Email
     FROM
CDSLayout.xls
(biff, embedded labels, table is [acs layout$]);

Regards,

Erich

Re: Displaying count of blank Emails

haymarketpaul — Fri, 13 May 2011 13:11:14 GMT

Brilliant - thanks Erich that's perfect