Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
=COUNT( {<PAT_ID= {"=Len(Trim(Enddate)) = 0"}>} PAT_ID)
Thanks. It works. By the way, why =COUNT( {<Enddate-={*}>} PAT_ID) is not working? I saw most people recommend this.
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)
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
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?
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.
Hi Sunny,
Do you have time to review my sample app? What is the problem? Thanks
Try this
='No enddate records: ' & COUNT(DISTINCT Aggr(If(Len(Trim(Enddate)) = 0, PAT_ID), PAT_ID, CITY, Enddate))
Works as expected. Thanks a million!!!