Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator
Creator

Simple count is not working properly

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)

16 Replies
twanqlik
Creator
Creator
Author

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

sunny_talwar

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

sunny_talwar

Try this:

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

swuehl
MVP
MVP

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)

twanqlik
Creator
Creator
Author

What a formula, but it works! Brilliant

twanqlik
Creator
Creator
Author

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)

swuehl
MVP
MVP

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.