Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Set analysis. Count empty cells

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:

image_1.bmp

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.

1 Solution

Accepted Solutions
jpenuliar
Valued Contributor III

Re: Set analysis. Count empty cells

this one may be the solution to your requirement:

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

6 Replies
Not applicable

Re: Set analysis. Count empty cells

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
Valued Contributor III

Re: Set analysis. Count empty cells

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
Valued Contributor III

Re: Set analysis. Count empty cells

might prove useful:

NULL handling in QlikView

jpenuliar
Valued Contributor III

Re: Set analysis. Count empty cells

this one may be the solution to your requirement:

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

Not applicable

Re: Set analysis. Count empty cells

Many thanks, Ashok.

Not applicable

Re: Set analysis. Count empty cells

Thanks a lot, Jonathan. It works.