How to show blank spaces instead of '0' for null values on a line graph?

[leale1997]

I have a data set attached to a line graph that looks like this:

Metric ABC	1/1/2016
Metric ABC	2/1/2016
Metric ABC	3/1/2016
Metric ABC	4/1/2016	1.9
Metric ABC	5/1/2016	0.7
Metric ABC	6/1/2016	0
Metric ABC	7/1/2016	0
Metric ABC	8/1/2016	0

When I graph this data in QlikView the Null values are set to 0.  So it appears as one continueous line with Null values read as 0.  I would like to represent Null Values as line breaks or blank spaces on the line chart. 

[sunny_talwar]

How about something like this for your expression?

If(Sum(Measure) > 0, Sum(Measure))

[johnw]

Here's an example with both one and two dimensions. It's important to show symbols as well as lines in case a month has nulls on either side, so there would be no line segment. The example is making the line transparent when it needs to be, and then using Sunny's expression for the symbols, so that we only get symbols when we should have them. The two-dimensional line chart is a bit more complicated, but I don't think is what you're after.

[leale1997]

Thank you so much for the quick response!

I am still having difficulty showing the sum of '0' while not showing Nulls.  Some of my measures do total to 0 and do need to be graphed.  While others are just blanks where there is no data and should read blank.  I am attaching something I tried to put together.  Please excuse my rudimentary formulas.  :0P

[johnw · Accepted solution]

Ah, I missed the null() vs. 0 thing, even though it was clearly stated. OK, the attached addresses that by checking that a count of non-null rows > 0 rather than that the sum of the rows > 0. Also using set analysis instead of sum(if()) as it's much more efficient on larger data sets.

[leale1997]

WOW! That worked like a charm : 0)

Can't even count how much time I spent clearing a space on my desk and beating my head against it.  I felt like Set Analytics was an option but sadly I am clueless with that method.  I am actually taking my first Set Analytics class next week. So this experience confirms I am missing a tool in my mind kit that I really need. Must drink coffee and really pay attention to the class next week.  Thanks again!

[johnw]

Set analysis is very powerful. You'll probably love it once you really understand it. My one caution is against loving it TOO much and using it in cases that might be better handled in some other way! It's just another tool in your mind kit, no matter how great a tool it may be.

[leale1997]

If I mihgt bother you with one more question.  I am trying to blend my conditional chart line color logic with the logic that you put into the analytics. 

Your logic looks like this:

if(count({<[Metric Desc.]={'Metric ABC'},[Performance]={"*"}>} Performance)

and above(count({<[Metric Desc.]={'Metric ABC'},[Performance]={"*"}>} Performance),-1),blue(),argb(0,0,0,0))

Mine looks like this:

=if(sum(if([Metric Desc.]='Metric ABC', Performance))<=1, RGB(0,128,0), RGB(255,0,0))

Mine is meant to make line and symbol colors red when the goal for metric is not met and green if it is met.  I am having difficulty molding mine into set analytics.

[johnw]

I think this?

if(count({<[Metric. Desc.]={'Metric ABC'},[Performance]={"*"}>} Performance)
and above(count({<[Metric. Desc.]={'Metric ABC'},[Performance]={"*"}>} Performance),-1),if(sum({<[Metric. Desc.]={'Metric ABC'}>} Performance)<=1,rgb(0,128,0),rgb(255,0,0)),argb(0,0,0,0))

And for the dots:

if(count({<[Metric. Desc.]={'Metric ABC'},[Performance]={"*"}>} Performance),if(sum({<[Metric. Desc.]={'Metric ABC'}>} Performance)<=1,rgb(0,128,0),rgb(255,0,0)),argb(0,0,0,0))