topic Re: Simple count is not working properly in QlikView

Simple count is not working properly

twanqlik — Mon, 09 Jan 2017 17:05:25 GMT

I have a simple table with only 4 fields and I want to calculate the distinct consumers that have an 'EmailTypeSent' = Welcome*.

This is working fine untill i filter in the EmailTypeSent on another value than Welcome*.

As is shown in the image, when i select 'Other' it still gives a 2 as count. However, it should give 0, because there are 0 email addresses in the filtered table with an 'EmailTypeSent' = Welcome*.

What is wrong with the used formula? The table shows the right records.

=count(DISTINCT{<EmailTypeSent={'Welcome*'}>} EmailAddress)

Re: Simple count is not working properly

sunny_talwar — Mon, 09 Jan 2017 17:23:08 GMT

Try this:

=Count(DISTINCT{<EmailTypeSent *= {'Welcome*'}>} EmailAddress)

Re: Simple count is not working properly

Anonymous — Tue, 10 Jan 2017 07:42:31 GMT

Hi,

This is because the set analysis is specific to Welcome son no matter what ypu select, it will only show the count where EmailTypeSent is "Welcome*"

Re: Simple count is not working properly

ahaahaaha — Tue, 10 Jan 2017 08:23:40 GMT

Hi,

Thaband rights, try

=count(DISTINCT{<EmailTypeSent={"Welcome*"}>} EmailAddress)

Regards,

Andrey

Re: Simple count is not working properly

twanqlik — Tue, 10 Jan 2017 09:33:00 GMT

Wauw this works, thank you! Now i'm trying to understand why your formula works and mine not

Not working:

=count(DISTINCT{<EmailTypeSent={'Welcome*'}>} EmailAddress)

Working:

=Count(DISTINCT{<EmailTypeSent *= {'Welcome*'}>} EmailAddress)

The only difference is the extra *.

Re: Simple count is not working properly

sunny_talwar — Tue, 10 Jan 2017 10:07:03 GMT

Read here about Implicit Set Operators

Re: Simple count is not working properly

twanqlik — Tue, 10 Jan 2017 12:31:45 GMT

Thank you that helps!

I have a similar question, maybe you can help me with that as well

Simple table, 2 colums:

EmailAddress

URL_CTA_Code

I want to calculate the number of distinct email addresses that have a 'bh' as URL_CTA_Code, however, the count is showing 9 and should be 0 in my example.

9 is the total count of unique email addresses. I noticed that this formula is only working when at least one consumer has a URL_CTA_Code, when they are all blank it doesn't work anymore. What i'm doing wrong?

=count({<URL_CTA_Code*={'bh'}>}DISTINCT EmailAddress)

Re: Simple count is not working properly

sunny_talwar — Tue, 10 Jan 2017 12:35:02 GMT

Not sure how your data model looks like, but may be try this

=Count({<URL_CTA_Code*={'bh'}>}DISTINCT URL_CTA_Code)

Assuming one - to - one relation between URL_CTA_CODE and EmailAddress fields.

Re: Simple count is not working properly

twanqlik — Tue, 10 Jan 2017 12:40:36 GMT

There is no data model, only one excel sheet with the 2 fields thats it.

Therefore there is no 1 - 1 relationship between the 2 fields. Your formula is showing the distinct count for the URL CTA Code without looking at the email addresses, that is missing

Anymore ideas ?

Re: Simple count is not working properly

sunny_talwar — Tue, 10 Jan 2017 12:46:37 GMT

Your expression seems to be working well for me

Re: Simple count is not working properly

twanqlik — Tue, 10 Jan 2017 12:54:05 GMT

I removed 2 'bh' values and now your expression it is not working anymore

Re: Simple count is not working properly

sunny_talwar — Tue, 10 Jan 2017 13:03:27 GMT

So you are saying that Code field is coming in as completely empty?

Re: Simple count is not working properly

sunny_talwar — Tue, 10 Jan 2017 13:07:21 GMT

Try this:

=Count({<Email={"=WildMatch(Concat(DISTINCT '|' & Code & '|'), '*|bh|*')"}>}DISTINCT Email)

Re: Simple count is not working properly

swuehl — Tue, 10 Jan 2017 13:20:17 GMT

Maybe just a simple expression like

=Count(DISTINCT If( Code = 'bh',Email))

If your data shows distinct Email per record, e.g. if Email is a primary key, it shouldn't perform worse.

edit:

Or create a flag in the script:

Table:

LOAD Email,

If(Len(Trim(Code)) > 0, Code) as Code,

If(Code='bh',1,0) as CodeFlag;

LOAD * Inline [

Email, Code

asa,

asdhsakm,

asdhsak,bh

sjdak,

];

then:

=Count({<CodeFlag = {1}>} DISTINCT Email)

Re: Simple count is not working properly

twanqlik — Tue, 10 Jan 2017 13:27:42 GMT

What a formula, but it works! Brilliant

Re: Simple count is not working properly

twanqlik — Tue, 10 Jan 2017 13:34:00 GMT

Thank you, this is also working.

Just wondering why my formula is not working? Why should it at least have an URL_CTHA_Code = bh value?

=count({<URL_CTA_Code*={'bh'}>}DISTINCT EmailAddress)

Re: Simple count is not working properly

swuehl — Tue, 10 Jan 2017 14:34:17 GMT

I think you don't need at least a code value of 'bh', but at least any value for that field (like my flag field does).

QV creates a record set with all records if the field you are trying to make a set expression selection in actually show NULL for all records.

Which is unexpected behaviour if your trying to filter on e.g. a certain value.

As far as I remember, there is at least a thread here in the forum that discusses that, but couldn't find it right now.