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: 
JustinDallas
Specialist III
Specialist III

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
Creator III
Creator III

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.


View solution in original post

11 Replies
vkish16161
Creator III
Creator III

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
Specialist
Specialist

Try distinct


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


or just to confirm your data try

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

vkish16161
Creator III
Creator III

Correcting an error in syntax:


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

vamsee
Specialist
Specialist

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
Creator III
Creator III

Np buddy.

JustinDallas
Specialist III
Specialist III
Author

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
Specialist III
Specialist III
Author

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
Creator III
Creator III

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
Specialist III
Specialist III
Author

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