Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
yvonne-c
New Contributor III

Include Null values

I have the expression below which is working fine - but I also want to include all IDs where Field "MoveOff" is empty - is there a way to add these into my set analysis?

Count({<Dateon={"<31/10/2018"},MoveOff={">01/10/2018"}ID)


Thanks

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Include Null values

If Eartag is an unique identifier for your table rows then you could use this expression:

 

Count({<
	Dateon= {"<$(=date('31/10/2018'))"},
	Eartag = E( {< MoveOff={"<$(=date('01/10/2018'))"}>})
>}ID)

 

Cheers

Vegar

 

#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
10 Replies

Re: Include Null values

May be this

Count({<Dateon = {"<31/10/2018"}, ID = {"=MoveOff > MakeDate(2018) or Len(Trim(MoveOff)) = 0"}>} ID)
MVP & Luminary
MVP & Luminary

Re: Include Null values

Another way:

Count({<Dateon={"<31/10/2018"},MoveOff={(">01/10/2018"|"=len(trim(MoveOff))=0"}>} ID)

- Marcus

Re: Include Null values

Marcus, if MoveOff is truly null, then the comparison to Len(Trim(MoveOff)) = 0 won't really work, I think. What do you think?
shwethaa
New Contributor III

Re: Include Null values

Try this, it may work!!

Count({$-<MoveOff={"*"}>+<Dateon={"<31/10/2018"},MoveOff={">01/10/2018"}>ID)


MVP & Luminary
MVP & Luminary

Re: Include Null values

With empty values it will work but not with NULL because if there is none value respectively NOTHING it couldn't be measured through itself - else an approach like in your example with measuring it with the help of a different field is needed.

- Marcus

Partner
Partner

Re: Include Null values

The "problem" with SET is that you can't select something that does not exists. You can't "select" Null() values with your modifer.

What if you instead exclude MoveOff prior to 01/10/2018

```
Count({<Dateon={"<31/10/2018"},MoveOff-={"<=01/10/2018"}ID)
```
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
yvonne-c
New Contributor III

Re: Include Null values

Thanks for all the replies - I just can't get it to work though Smiley Sad

Link to some sample data here I hope  CountTest - don't seem to be able to attach excel file?!

 

 

 

Partner
Partner

Re: Include Null values

If Eartag is an unique identifier for your table rows then you could use this expression:

 

Count({<
	Dateon= {"<$(=date('31/10/2018'))"},
	Eartag = E( {< MoveOff={"<$(=date('01/10/2018'))"}>})
>}ID)

 

Cheers

Vegar

 

#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
shwethaa
New Contributor III

Re: Include Null values

Hey Try below expression. It is working for given data.
Count=4
=Count({-<MoveOff={"*"}>+<Dateon={"<31/10/2018"},MoveOff={">01/10/2018"}>} ID)

-Shwetha