Incorrect Null count in straight table

[clondono]

Hello,

I have an issue with counting the number of rows containing Null value. 

I'm trying to count the number of DISTINCT projects that have NULL values for the "Facility" field and a count of projects with "End dates" in the past. Not all projects have null values for this field and some projects might have more than one row with null value for the field.

If I create two different straight tables, one for end dates in the past and one for missing facilities, I get the correct count.  However, I cannot get both fields in a single straight table because the missing facilities count is incorrect then.  I am attaching a sample file with the error.

Any ideas on how to solve this issue?

Thanks!

[cbaqir]

sunindia‌

[sunny_talwar · Accepted solution]

Not sure if this would still give you the correct number when you clear, but try this:

Count(DISTINCT{$<REQUEST_ID = {"=NullCount(TASK_FACILITY)>0"}>*<Flag_NullTaskSeq = {'*'}>}REQUEST_ID)

[clondono]

Thank you Sunny, you are awesome!

This solution works, also I found a solution from a post you responded a couple of minutes ago and I applied to this issue and it worked as well (Count number).

This is the expression I was using from the alternative solution:

Sum(Aggr(If(NullCount(TASK_FACILITY)>0, 1, 0),REQUEST_ID, [TASK_FACILITY]))

[sunny_talwar]

Great, I am glad I was able to help. But just so you know, prefer using Set Analysis over Aggr() wherever possible.