Cannot get a 0 just Null values

[helen_pip]

Hello

Could someone kindly look at the following expression I have written in a Pivot table

I would like to show 0 values if the sum of inline_Monthly_Data_A is Null

If

(IsNull(Sum(Inline_Monthly_Data_A)),0,sum(Inline_Monthly_Data_A)))

With the above I am getting - values

Can anyone help?

Kind Regards

Helen

[#2]

Hi Helen,

Try this:

rangesum(Sum(Inline_Monthly_Data_A),0)

Hope this helps!

Gabriela.

[helen_pip]

Hello Gabriela

Thank you for your suggestions, but alas, I am still getting Null values

Thanks

Helen

[#4]

In the properties of your pivot table in presentation tab, does the option "populate missing cells" is checked?

I think the problem is that they are missing values instead of null values, if you check that option you can treat missing values as null values.

[helen_pip]

Thank you Gabriela

I have found your suggestion very useful and this helps me now

Thanks for your help

Helen

[francescopuppin]

Hi Helen,

it all depends on the "origin" of that NULL.

In general, Qlikview gives your required result by default, with no need of any additional effort.

Let me give you an example.. take this script:

LOAD * INLINE [

Field1, Field2

a,10

a,20

b

b

b

];

Now, b is always associated with NULL, so you would expect to see a NULL in the chart...

If you make a pivot chart with

Dimension: Field1

Expression1: Sum(Field2)

you will see only a,30

But if you add another expression

Expression2: 1

You will see also the record of b, with 0 as you required, although it is a sum of NULLs.

So, probably your NULLs are coming from a special situation (like a LEFT join.. or simply an association with missing values?...)

You should try to reproduce your error in a mini-script. This is the most difficult part. Once you have reproduced it, it's very easy to find a solution.

In alternative, if you want, you can send me the qvw and I will fix it for you

Regards,

F.