Counting NULL in a pivot chart

[#1]

Hello

This question appears on other threads but none of the proposed solutions work (for example, see thread: http://community.qlik.com/forums/t/24728.aspx)

All I want to do is count the number of NULL values from a field in a pivot column. Intuitively, this is what I would do:

COUNT( { $ < Transaction = {} > } ID)

This does not work. The stats box doesn't show any NULLs but another thread suggests that stats boxes are generally not to be trusted (especially with NULLs in version 9).

Other threads suggest the following. None of these work for me.

Count({$-<Transaction={}>} ID)
Count({$<Transaction= {$(=null())}>} ID)
Count({$<isnull(Transaction)= {'-1'}>} ID)
COUNT({$-<Transaction = -{}>} ID)

My workaround is to create a separate column of data on load in the script, i.e. IF(ISNULL(TransactionID), 0, 1) AS TransactionIDIsNull and then count the 1s and 0s.

This works but I was hoping for a tidier solution.

Thanks in advance
James

[Anonymous]

Rather question than an answer:
Why use set analysis? Did you try this:
count(if(isnull(Transaction),ID))

[#3]

Thanks for your quick reply. That does indeed work.

However, I simplified the problem in my post. My actual expression is of the form:

count ( { $ < Region = {}, var1 = {val1}, var2 = {val2} > } )

So I have other set analyses going on. I was after a set analysis-related answer, if at all possible.

With thanks
James

[Anonymous · Accepted solution]

James,
in this case, I'd be looking for a solition in this direction, combining the above expression with set analysis:
count({$<other set analysis conditions>} if(isnull(Transaction),ID))

[#5]

That worked. Fantastic. Thanks very much indeed !!!