Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wim_embrechts
New Contributor III

Count ALL issue

Hi,

In a measure I'm making a count of the file numbers. This seems to be a distinct count.

This is the measure that I'm using:

count(DOSSIERNR)

But how do I modify the measure so I also count the duplicate file numbers?

I've been experimenting with some variations but without succes.

Example:

file number

101

102

102

103

104

The measure count(DOSSIERNR) results in 4 but I also need a measure that counts 5 in this case.


Many thanks in advance for your assistance.

Wim

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count ALL issue

Seems like DOSSIERNR is a key field. It might make sense to count another field which is not a key field. For example, lets say you want to count DOSSIERNR from your fact table which look like this


LOAD DOSSIERNR,

     AnotherField,

...

FROM ....;

Then may be do a count like this

Count(AnotheField)

or

Count({<DOSSIERNR = {'*'}>}AnotherField)

6 Replies
sureshqv
Esteemed Contributor III

Re: Count ALL issue

try simple way

count(Distinct DOSSIERNR)

arvind_patil
Valued Contributor II

Re: Count ALL issue

Hi  Wim,

I agree  with chanty. Use  His code.

count(Distinct DOSSIERNR)


Thanks,

Arvind Patil

stigchel
Honored Contributor

Re: Count ALL issue

In your example the Count([file number]) will result in 5 and Count( DISTINCT [file number]) will result in 4. So when you say that Count(DOSSIERNR) results in 4, there must be something else going on. You might be using a dimension (Afdeling?) that has no value for one of the DOSSIERNR rows.

MVP
MVP

Re: Count ALL issue

Seems like DOSSIERNR is a key field. It might make sense to count another field which is not a key field. For example, lets say you want to count DOSSIERNR from your fact table which look like this


LOAD DOSSIERNR,

     AnotherField,

...

FROM ....;

Then may be do a count like this

Count(AnotheField)

or

Count({<DOSSIERNR = {'*'}>}AnotherField)

sureshqv
Esteemed Contributor III

Re: Count ALL issue

check sunny example here

How to load distinct value?

wim_embrechts
New Contributor III

Re: Count ALL issue

Hi Sunny, Chanty, Arvind and Piet Hein,

Thank you all for your valuable feedback.

Count distinct or just count have the same result and it was what Sunny wrote that made me thinking.

In the Data Model Viewer the field Dossiernr is not marked as a key but I found the cause of the 'strange' behaviour. Also in the Data Model Viewer I noticed that the field Dossiernr has no duplicates. From that point of view it seems perfectly normal that a count and a count distinct have the same result.

The solution offered by Sunny using set analysis worked great! Thanks for that.

Wim