topic Re: Is Null in Set Analysis in QlikView

Is Null in Set Analysis

Thu, 20 Sep 2012 18:29:19 GMT

Help. I am just trying to convert the If/Then SUM expression below, that works fine but is slow, to Set Analysis. I don't know how to get the IsNull to work in Set Analysis despite searching forums. Seems like this should be a simple modification.

Can anyone please assist??

Thanks MUCH.

Original If/Then that works fine:

= sum(if(IsNull(TCW) and TCNB=0,TCAmt))

Trying to convert to Set Analysis, have tried this one and many others with no luck. :

=sum({<TCNB={0}, $(TCW=("")"} TCAmt))

Re: Is Null in Set Analysis

MayilVahanan — Thu, 20 Sep 2012 18:34:13 GMT

Try like this,

=Sum({<TCNB ={0],TCW={''}>}TCAmt)

Hope it helps

Re: Is Null in Set Analysis

Thu, 20 Sep 2012 18:45:11 GMT

This resulted in no data. So no luck.

Re: Is Null in Set Analysis

MayilVahanan — Thu, 20 Sep 2012 19:30:14 GMT

Hi,

Try this

=Sum({<TCNB ={0],TCW-={*}>}TCAmt)

Re: Is Null in Set Analysis

Anonymous — Thu, 20 Sep 2012 19:42:43 GMT

I assume this will work:

=sum({($ -<TCW={'*'}>) * <TCNB={'0'}>} TCAmt)

Regards,

Michael

Re: Is Null in Set Analysis

Thu, 20 Sep 2012 20:53:43 GMT

Well this one seems to be close, the others are not working at all. (name blanked out) It returns the correct SUM in total, but when drilling into details in all cases the If/Then returns data for the Name, and in SOME cases the Set Analysis returns data as well. But in SOME cases it returns NULL (see below for side by side example).

You can see the TCW and TCNB values are 0 and NULL, as they should be. Just can't figure out why a bunch of the names are returning NULL, however working fine with If/Then.

Any other tweaks that might solve this? Thank you again.

Re: Is Null in Set Analysis

Thu, 20 Sep 2012 20:58:32 GMT

Mayil is there a typo in the formula? QV is asking for a comma for some reason and giving a syntax error when i try this expression.

Thank you.

Re: Is Null in Set Analysis

MayilVahanan — Thu, 20 Sep 2012 21:01:29 GMT

Its a bug in expression validation ,ignore it. And check the result.. Expression is fine.

Re: Is Null in Set Analysis

Thu, 20 Sep 2012 21:07:48 GMT

OK, i tried it

=Sum({<TCNB ={0],TCW-={*}>}TCAmt)

I think the square bracket after the 0 should be curly bracket right? Regardless i tried it both ways and recieved 0 for all records.

Re: Is Null in Set Analysis

Anonymous — Fri, 21 Sep 2012 12:31:57 GMT

Set analysis evaluates data over the complete data set, it "doesn't know" about the chart dimensions. That means that set analysis modifiers can be used in a chart expression only if they're applicable to any row regardless chart dimensions.
Apparently it is not the case in your example, hence you get correct result in total but wrong per dimension. Using "if" is the reliable approach here.

Regards,
Michael

Re: Is Null in Set Analysis

Fri, 21 Sep 2012 12:44:56 GMT

Thanks Michael. I was beginning to come to that conclusion as well. I will likely still try and determine why the data model and set analysis works perfectly in some of the results but returns blanks in others. Seems odd but of course there is a reason, just need to find it. I am sure it has something to do with the key table I have created with Outer Joins.

In the mean time, will stick with the IF, as I have 'bigger fish to fry' in this dev effort.

Appreciate all the feedback!

Re: Is Null in Set Analysis

Tue, 25 Sep 2012 21:04:35 GMT

FYI - I tried this formula in a different element and it worked perfectly. So marked as correct answer.

THANKS!

Re: Is Null in Set Analysis

Thu, 02 Jul 2015 05:26:15 GMT

Hi All, FYI

[Field_name]=e({<[Field_name]={''}>} [Field_name]) - if we want NOT ISNULL ([Field_name])

Re: Is Null in Set Analysis

jmatarini2015 — Mon, 26 Aug 2019 13:23:18 GMT

Hi Micheal,

Requesting to help me understand {($ -<TCW={'*'}>) * <TCNB={'0'}>}

Thanks & Regards,

Das