set analysis: fetch null rows

[#1]

Hi,

I have this expression in chart

SUM({<status-={5}>}saleamount)

I have unchecked suppress null values and also unchecked supprse zero and missing values. Can someone please tell me why would this expression still not fetch the null rows?

Shah

[Anonymous]

sum only works with real values (not NULL)

try rangesum instead

[#3]

but I do see those null values if I remove the set analysis and then uncheck the suppress zero and suppress missing values.

I have the product on dimensions so I would like to see those products that have null sale amount. We do have those empty sale amount against many products but the chart is not showing those if I have the set analysis. If I remove this set analysis, then it seems to work

Shah

[jonathandienst]

Hi

Set analysis works in the same way as manual selections. In manual selections, you cannot select NULL values, and the same applies to set expressions. You have these options:

• Use NULLASVALUE on the field in the load script so that a null behaves like an empty string and is selectable. {<Status={''}>}
• Set a Null flag in your load script {<NullFlag={1}>}
• Use  Sum(If(IsNull(Status) Or Status <> 5, saleamount)  (but that may perform poorly on a large data set)

HTH

Jonathan

Edit: the IsNull(Status) is redundant here

[Anonymous]

did you try if Statement to ao´void set Analysis?

if (Status<> 5, sum(saleamount))

[#6]

my data set is close to 10 million so I would rather prefer a set analysis

[jonathandienst]

The first two options in my post use set analysis and should perform well.

[Anonymous]

sure thats the better solution

maybe this might help: multiply your sum with the Boole value *-1 if Status<>5

sum(Saleamount)*(-1*(Status<>5)

you might build a flag in  the script

if (Status<>5,1,0) as StatusFlag

then you could use

sum(Saleamount)*StatusFlag

I had something similiar and it was faster than IF conditions