12 Replies Latest reply: Jul 2, 2015 1:26 AM by Michael Yachnik

# Is Null in Set Analysis

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.

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

Hi

Try like this,

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

Hope it helps

• ###### Re: Is Null in Set Analysis

This resulted in no data.   So no luck.

• ###### Re: Is Null in Set Analysis

Hi,

Try this

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

• ###### Re: Is Null in Set Analysis

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

Hi

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

• ###### Re: Is Null in Set Analysis

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

I assume this will work:

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

Regards,

Michael

• ###### Re: Is Null in Set Analysis

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

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

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

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

Hi All, FYI

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