Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Substitute Zero for NULL Value

I just can't make heads or tails of Qlikview, nothing ever seems to make sense in this tool. 

I have a field which may contain NULL values.  They display in my pivot table as a "-" which I do not want.  In SSRS or any other tool I can write an expression to handle this and change the "-" to 0.  How do I do this in Qlikview?

Right now i'm doing the following:

IF(IsNull(SUM(diff_clicks)),0,SUM(diff_clicks))

But this does not work, I still get the "-" in the fields where there is no number.

I need someone, in plain English and easy to follow steps, to explain to me how to do this.

8 Replies
disqr_rm
Partner - Specialist III
Partner - Specialist III

Try

NumSum(SUM(diff_clicks))

NumSum will return 0 for a null value.

Not applicable
Author

Try Rangesum(SUM(diff_clicks)). The NumSum function is now obsolete.

Anonymous
Not applicable
Author

I might be wrong, but as fare as i remember there is at property in the pivot table of how null values are displayed.

Henrik

Not applicable
Author

Hi!

Go to Presentation Tab:

See the red Circle in Attached File.

Write 0 instead of '-' .

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try using the Alt method like below

=Alt(SUM(diff_clicks), 0)


Regards,

Jagan.

SunilChauhan
Champion II
Champion II

lF(IsNull(SUM(diff_clicks)),0,SUM(diff_clicks))

or


lF(len(SUM(diff_clicks)),0,SUM(diff_clicks))

or

lF((SUM(diff_clicks))=' ',0,SUM(diff_clicks))

hope this helps

Sunil Chauhan
Anonymous
Not applicable
Author

Lots of good stuff here, but if you have no data for a particular cell in a straight or pivot table, for example, none of these expressions will even be executed.

You can take advantage of QlikView's loosely typed nature and the only operator that doesn't return null when one of its operands is null: &.

For example, 0+NULL = NULL, while 0&NULL = 0. It's also important to note that 0&0 = 00.

So, something like num(0&sum(Sales),'0') can make a value appear where there was none before. You may also need set analysis with {1} to ensure all possible dimension values appear.

Hope this helps!

srinivasa1
Creator II
Creator II

In Dimensions clik on "suppress when value is Null".it may help