topic Re: replacing null values with 0 in QlikView

replacing null values with 0

Mon, 25 Nov 2013 09:49:22 GMT

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

Re: replacing null values with 0

er_mohit — Mon, 25 Nov 2013 09:52:26 GMT

try to do in chart properties presentation tab

below is null symbol - replace it to zero

your expression is right

Re: replacing null values with 0

MK_QSL — Mon, 25 Nov 2013 09:56:50 GMT

try this

Re: replacing null values with 0

Mon, 25 Nov 2013 10:01:56 GMT

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!

Re: replacing null values with 0

Mon, 25 Nov 2013 10:03:51 GMT

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.

Re: replacing null values with 0

MK_QSL — Mon, 25 Nov 2013 10:09:40 GMT

Use something like below...

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

Re: replacing null values with 0

jagan — Mon, 25 Nov 2013 10:23:16 GMT

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.

Re: replacing null values with 0

swuehl — Mon, 25 Nov 2013 10:23:21 GMT

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.