topic Re: Cannot get a 0 just Null values in QlikView

Cannot get a 0 just Null values

helen_pip — Tue, 15 Jan 2013 14:35:55 GMT

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

(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

Re: Cannot get a 0 just Null values

Tue, 15 Jan 2013 14:46:36 GMT

Hi Helen,

Try this:

rangesum(Sum(Inline_Monthly_Data_A),0)

Hope this helps!

Gabriela.

Re: Cannot get a 0 just Null values

helen_pip — Tue, 15 Jan 2013 14:50:09 GMT

Hello Gabriela

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

Thanks

Helen

Re: Cannot get a 0 just Null values

Tue, 15 Jan 2013 15:00:50 GMT

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.

Re: Cannot get a 0 just Null values

helen_pip — Tue, 15 Jan 2013 15:38:20 GMT

Thank you Gabriela

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

Thanks for your help

Helen

Re: Cannot get a 0 just Null values

francescopuppin — Tue, 15 Jan 2013 16:01:25 GMT

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

];

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,