Count doesn't work

[JustinDallas]

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.

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

[vkish16161 · Accepted solution]

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]

Try distinct

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

or just to confirm your data try

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

[vkish16161]

Correcting an error in syntax:

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

[vamsee]

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]

Np buddy.

[JustinDallas]

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]

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]

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]

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