16 Replies Latest reply: Jan 10, 2017 9:34 AM by Stefan Wühl

# 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.

• ###### Re: Simple count is not working properly

Try this:

• ###### Re: Simple count is not working properly

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

Hi,

Thaband rights, try

Regards,

Andrey

• ###### Re: Simple count is not working properly

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

Not working:

Working:

The only difference is the extra *.

• ###### Re: Simple count is not working properly

Thank you that helps!

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

Simple table, 2 colums:

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?

• ###### Re: Simple count is not working properly

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

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

Your expression seems to be working well for me

• ###### Re: Simple count is not working properly

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

• ###### Re: Simple count is not working properly

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

• ###### Re: Simple count is not working properly

Try this:

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

• ###### Re: Simple count is not working properly

What a formula, but it works! Brilliant

• ###### Re: Simple count is not working properly

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:

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

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

Email, Code

asa,

asdhsakm,

asdhsak,bh

sjdak,

];

then:

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

• ###### Re: Simple count is not working properly

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?

• ###### Re: Simple count is not working properly

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.