Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Partner
Partner

Substitute Zero for NULL Value

Try

NumSum(SUM(diff_clicks))

NumSum will return 0 for a null value.

Not applicable

Substitute Zero for NULL Value

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

henrikmatz
Contributor II

Substitute Zero for NULL Value

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

Re: Substitute Zero for NULL Value

Hi!

Go to Presentation Tab:

See the red Circle in Attached File.

Write 0 instead of '-' .

MVP & Luminary
MVP & Luminary

Substitute Zero for NULL Value

Hi,

Try using the Alt method like below

=Alt(SUM(diff_clicks), 0)


Regards,

Jagan.

SunilChauhan
Esteemed Contributor

Substitute Zero for NULL Value

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

Highlighted
flyingcheesehea
Contributor II

Re: Substitute Zero for NULL Value

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

Re: Substitute Zero for NULL Value

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