replacing null values with 0

[#1]

Hi,

I'm trying to replace a null value with 0 in an expression which contains a set analysis but it doesn't work and I think I'm going to kill myself.

The expression is the following:

sum({<YearMonth={$(=AVG(TOTAL <MAT_MATERIAL_CODE> AGGR( MAX( {$<MONTH=, Quarter=>} YearMonth), MAT_MATERIAL_CODE)))} Cost).

I've tried to use if isnull or alt(sum(....), 0) but no success. I've realized it's because of the set analysis because if I apply these solutions to another expression which not contains set analysis it works.

Do you have any ideas?

Thanks

Cristina

[er_mohit]

try to do in  chart properties presentation tab

below is null symbol -  replace it to zero

your expression is right

[MK_QSL]

try this

[#4]

Hi Cristina,

Go to Properties--Presentation tab of the chart and in the left side, at the bottom, you will have an option to change the Null Symbol, you can give 0 here:

Hope this helps!

[#5]

It is not a solution because I need to summarize this expression with another one which has a value. I can't add a value to null, because it results null. First, I need to replace null with zero and then I think it'll work.

[MK_QSL]

Use something like below...

if(SalesValue=null(), 0, SalesValue)

[jagan]

Hi,

Why can't you change the Null value to zero in script?  It would be easier like this

LOAD

*,

Alt(Measure, 0) AS Measure

FROM Datasource;

Hope this helps you.

Regards,

Jagan.

[swuehl]

You can use rangesum() function to add the values, NULL will be treated as zero.

Have you double checked that the dollar sign expression returns something meaningful?

And besides your problems with NULL, I think it could create issues if you average a YearMonth field and then want to assign this average value to YearMonth Field.