Is Null in Set Analysis

[#1]

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))

[MayilVahanan]

Hi

Try like this,

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

Hope it helps

[#3]

This resulted in no data.   So no luck.    

[MayilVahanan]

Hi,

Try this

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

[Anonymous · Accepted solution]

I assume this will work:

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

Regards,

Michael

[#6]

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.

[#7]

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.

[MayilVahanan]

Hi

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

[#9]

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.  

[Anonymous]

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