topic Set Analysis for Null and Not Null Values in QlikView

Set Analysis for Null and Not Null Values

Thu, 30 Jul 2015 12:48:51 GMT

Hi,

I'm trying to create a text object indicating the number of unassigned, open tickets. When I try to use this formula, I think that the $- part of the set analysis is applying to both conditions that I've set, because the number is wildly inaccurate.

=count({$-<actorname={'*'}, Status={'Open'}>} RequestID)

Another way I tried to do this was by subtracting the number of tickets with an open and not null actorname value from the total count of open tickets, but when I validated my data, I found that it wasn't working either. For that, I used this formula:

=count({<Status={'Open'}>} RequestID)-count({<actorname={'*'}, Status={'Open'}>} RequestID)

Is there a way to get the count of tickets with null actorname values which also have a status of 'Open'?

Thanks

Re: Set Analysis for Null and Not Null Values

antoniotiman — Thu, 30 Jul 2015 12:52:29 GMT

Try

Count({<actorname-={*},Status={'Open'}>} RequestID

Re: Set Analysis for Null and Not Null Values

Thu, 30 Jul 2015 12:54:41 GMT

try =count({$-<actorname={'*'}> * <Status={'Open'}>} RequestID)

Re: Set Analysis for Null and Not Null Values

Thu, 30 Jul 2015 12:57:10 GMT

try like this

=if(isnull(actorname),count({$<Status={'Open'}>}RequestID))

Re: Set Analysis for Null and Not Null Values

Thu, 30 Jul 2015 12:58:21 GMT

When I do this I get the same result as I did for the (count({$-<actorname={'*'}, Status={'Open'}>} RequestID)) formula I tried earlier.

Re: Set Analysis for Null and Not Null Values

Thu, 30 Jul 2015 12:58:46 GMT

This formula gives me a result of 0.

Re: Set Analysis for Null and Not Null Values

antoniotiman — Thu, 30 Jul 2015 13:01:46 GMT

I'm sorry, I forgot ')' at end of expression

Re: Set Analysis for Null and Not Null Values

Anonymous — Thu, 30 Jul 2015 13:02:34 GMT

you may be not having exactly null in your data base some time its blank. use len() function .

So use if(len(actorname)>0,count({$<Status={'Open'}>}RequestID))

Thanks

BKC

Re: Set Analysis for Null and Not Null Values

Thu, 30 Jul 2015 13:03:04 GMT

This is returning the total number of open tickets, but I don't think the null actor value is narrowing it down any further.

Re: Set Analysis for Null and Not Null Values

jonathandienst — Thu, 30 Jul 2015 13:07:33 GMT

Well nulls will never be part of the current selection, so you needs something like this:

=count({1<actorname -= {'*'}, Status={'Open'}>} RequestID)

or this:

=count({1<actorname -= {'*'}> * $<Status={'Open'}>} RequestID)

If these do not work, I suggest you post your application or at least a sample illustrating the problem.

Re: Set Analysis for Null and Not Null Values

sasiparupudi1 — Thu, 30 Jul 2015 13:09:22 GMT

Count({<actorname-={"''"},Status={'Open'}>} RequestID)

Count({<actorname-={"'-'"},Status={'Open'}>} RequestID)

Re: Set Analysis for Null and Not Null Values

jvitantonio — Thu, 30 Jul 2015 13:12:08 GMT

Hi, you can try using NullAsValue in your script as represented in the attached file.

I hope this helps.

JV !

Re: Set Analysis for Null and Not Null Values

Thu, 30 Jul 2015 13:26:04 GMT

Thank you!

Re: Set Analysis for Null and Not Null Values

sasiparupudi1 — Thu, 30 Jul 2015 13:27:12 GMT

=count({1<actorname={"$(Null())"},Status={'Open'}>} RequestID)

Re: Set Analysis for Null and Not Null Values

anilmahanty — Mon, 14 Nov 2016 08:00:56 GMT

This is working in QV 12, not in QV 11.