Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor

Count doesn't work

Hello Everyone,

I must be going blind, but I have my Count set analysis not working and I've used it a million times before.  My text looks like so:

Count({<IsLessThanFiveDaysOut={1}>} [Trailer ID])

However, when I use this in a KPI callout, it counts every record.


BadImage.png

The count value reads 1.03K, when in fact it should be zero because all the records have a Null value..

BadImage2.png

1 Solution

Accepted Solutions
vkish16161
Contributor III

Re: Count doesn't work

This will definitely won't work.

This is happening because  "IsLessThanFiveDaysOut" doesn't have a value for set analysis to work.

So it is giving the count of [Trailer ID].

So, as a matter of fact you can try  using Count ([Trailer ID]) in a KPI box.


Both will yield same result.


11 Replies
vkish16161
Contributor III

Re: Count doesn't work

This will definitely won't work.

This is happening because  "IsLessThanFiveDaysOut" doesn't have a value for set analysis to work.

So it is giving the count of [Trailer ID].

So, as a matter of fact you can try  using Count ([Trailer ID]) in a KPI box.


Both will yield same result.


vamsee
Valued Contributor

Re: Count doesn't work

Try distinct


Count({DISTINCT<IsLes sThanFiveDaysOut={1}>} [Trailer ID])


or just to confirm your data try

Concat({DISTINCT<IsLessThanFiveDaysOut={1}>} [Trailer ID], '|')

vkish16161
Contributor III

Re: Count doesn't work

Correcting an error in syntax:


Count({<IsLes sThanFiveDaysOut={1}>} DISTINCT [Trailer ID])

vamsee
Valued Contributor

Re: Count doesn't work

Thanks I missed that braces.

Concat(DISTINCT{< IsLessThanFiveDaysOut = {'1'} >} [Trailer ID], '|')

Anyway your answer was right as the column is null it would count the trailer id's

vkish16161
Contributor III

Re: Count doesn't  work

Np buddy.

JustinDallas
Valued Contributor

Re: Count doesn't work

I don't understand.  If there is no value that matches what is in the Set Analysis, then shouldn't it return 0?  The Set Analysis statement I've got in SQL would be

"SELECT COUNT(*) FROM VegetarianMenu WHERE ItemType='Meat'"

In this case, the answer should be 0, it shouldn't count every menu item possible.

JustinDallas
Valued Contributor

Re: Count doesn't work

Wait, if the column is Null, then it SHOULDN'T count it right?  Hell, even in some languages NULL != NULL, much less NULL != '1'

vkish16161
Contributor III

Re: Count doesn't  work

Nulls/Missing values are handled differently. Your anticipation is correct. I'm assuming your field has missing values.

As a check, in a filterpane, try this:

Instead of IsLessThanFiveDaysOut use isnull (IsLessThanFiveDaysOut).


This will reveal if nulls exist.

JustinDallas
Valued Contributor

Re: Count doesn't work

Thanks for the debugging tip!!!!  I tried it, and it concatenated every single ID in the datamodel.

Community Browser