Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

helen_pip
Contributor II

Cannot get a 0 just Null values

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

5 Replies
Not applicable

Re: Cannot get a 0 just Null values

Hi Helen,

Try this:

rangesum(Sum(Inline_Monthly_Data_A),0)

Hope this helps!

Gabriela.

helen_pip
Contributor II

Re: Cannot get a 0 just Null values

Hello Gabriela

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

Thanks

Helen

Not applicable

Re: Cannot get a 0 just Null values

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
Contributor II

Re: Cannot get a 0 just Null values

Thank you Gabriela

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

Thanks for your help

Helen

francescopuppin
New Contributor III

Re: Cannot get a 0 just Null values

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.

Community Browser