Set analysis. Count empty cells

[#1]

Hi there.

My problem has to do with empty cells and how to consider them in set analysis expressions.

I've got the following data:

And I want a measure to count those queries not yet replied. I.e, something like:

Count({$<Query_date={"*"},Response_date={''}>}Id)

But I can't figure out what to put instead of the red bit {''} to make it work.

The cells in the Excel file have no content.

I've been reading about null() and unsuccessfully tried different alternatives, like:

Count({$<Query_date={"*"},Response_date={""}>}Id)

Count({$<Query_date={"*"},Response_date={"null"}>}Id)

Count({$<Query_date={"*"},Response_date={'null}>}Id)

Count({$<Query_date={"*"},Response_date=isnull()>}Id)

Nothing works.

I'd appreciate it very much any guidance on the subject.

Thanks in advance.

M.

[#2]

Hi ,

In the backend repalce nulls with some symbol and in front end set analysis menstion that in expression.

In Backend;

if(Isnull(Date),1,Date) AS [New Date]

Expression:

count{<NewDate={'1'}>id}

Hope this help you.

Thanks,

Ashok.

[jpenuliar]

Cells can be Null or Blank. Depends on your data, you can flag the Blanks or Null in your load Script with

Null() or Len() logic.

[jpenuliar]

might prove useful:

NULL handling in QlikView

[jpenuliar · Accepted solution]

this one may be the solution to your requirement:

Re: How to use Null() into a set analysis expression

[#6]

Many thanks, Ashok.

[#7]

Thanks a lot, Jonathan. It works.