How to get record count for null (blank or missing) ?

[baylor2016]

Hi,

After reviewing many posts related to null(missing, blank) fields, I still cannot figure out how to get the correct counts for field whose value is null, missing or blank.  can get the non-null counts by using =COUNT( {<Enddate={*}>} PAT_ID). For count of null field I used =COUNT( {<Enddate-={*}>} PAT_ID), it always return 0. I tried many different methods such as =COUNT( {<Enddate-={'-'}>} PAT_ID), still cannot get it right. Please advice. The sample project is attached.

Thanks

Longmatch

[sunny_talwar · Accepted solution]

Try this

=COUNT( {<PAT_ID= {"=Len(Trim(Enddate)) = 0"}>} PAT_ID)

[baylor2016]

Thanks. It works. By the way, why =COUNT( {<Enddate-={*}>} PAT_ID) is not working? I saw most people recommend this.

[sunny_talwar]

Set Analysis only work on a non-null cell value... wherever Enddate is null, it won't work.... You can create a flag in the script as an alternate solution

If(Len(Trim(Enddate)) = 0, 1, 0) as EnddateNullFlag

and then use this

=Count({<EnddateNullFlag = {'1'}>} PAT_ID)

[baylor2016]

Hi Sunny,

I used the expression you recommended in Textbox and Straight table in the same sample project, but it behaves differently. The number returned is not what I expected.

=COUNT( {<PAT_ID= {"=Len(Trim(Enddate)) = 0"}>} PAT_ID)

Please refer to the attachment for details. Thanks

Longmatch

[sunny_talwar]

You are not doing a count, you are doing a distinct count.... since distinct count depends on dimension (startdate in this case)... table shows more than 3....what do you expect to see in straight table or text box object?

[baylor2016]

I would like to get the count of patients whose address have no endDate.  It sounds simple, but it is not easy to understand. Thank you for your always helps.

[baylor2016]

Hi Sunny,

Do you have time to review my sample app? What is the problem? Thanks

[sunny_talwar]

Try this

='No enddate records: ' & COUNT(DISTINCT Aggr(If(Len(Trim(Enddate)) = 0, PAT_ID), PAT_ID, CITY, Enddate))

[baylor2016]

Works as expected. Thanks a million!!!