Count returns incorrect result

[j0nimost1]

I am currently trying to execute a count statement with conditions as shown below;

Count(${<AdmissionStatus={'In Patient'},InvoiceType={5}>}distinct AssessmentId)

However, the resulting value is extremely exaggerated that is 2.4 Million. Running the same query in the database;

SELECT Count(AssessmentId) FROM ClaimsMaster WHERE AdmissionStatus='In Patient' AND InvoiceType=5

returns 23,500.

I expect the count value in like sense to match what is in the database.

[ArnadoSandoval]

Hi J0nimost,

It is very likely your tables associations are incorrect, you should open your data model checking all the associations, one of them may have a one-to-many introducing misleading results.

Hope this helps,

Arnaldo Sandoval.

[tresesco]

---

@ArnadoSandoval wrote:

Hi J0nimost,

It is very likely your tables associations are incorrect, you should open your data model checking all the associations, one of them may have a one-to-many introducing misleading results.

Hope this helps,

Arnaldo Sandoval.

---

And, I would say even many-to-many relationship is also possible that could result this.

[lorenzoconforti · Accepted solution]

I think your expression is incorrect; try this

Count({$<AdmissionStatus={"In Patient"},InvoiceType={5}>}distinct AssessmentId)

Also, if InvoiceType is a text field, enclose the 5 in double quotes

[tresesco]

Hi @lorenzoconforti , I don't know if you are saying the expression incorrect just because of quotes differences. If that is the case, I would say that you are actually proposing the opposite. Single quotes are used for string comparison, whereas double quotes are for search string. Apart from that single quotes make the search case-sensitive while double quotes make search case-insensitive . For numeric value comparison also - in set analysis it is always text-search, hence you include numeric value in quotes or not - would not make a difference in result output.

You can refer : https://community.qlik.com/t5/Qlik-Design-Blog/Quotes-in-Set-Analysis/ba-p/1471824 for better understanding.

[lorenzoconforti]

@tresesco 

The main issue is the dollar sign that it's in the wrong place

Thanks for the link though

[j0nimost1]

I found out that actually the data is Ok. Problem is that Qlik sense Count is counting all the AssessmentIds without checking the conditions set

[j0nimost1]

Awesome !!!!!