missing data

[mazacini]

Hi

I have a couple of measures (A & B) which I am plotting across the months of the year.

But for measure A, I only have values for the first 4 months of the year. Measure B has a value for each of the 12 months.

The problem is the graph shows Measure A as having a zero value for months 5 thru 12.

I only want to show the line for the actual months 1 to 4 for which there is a value, and hide the graph for months 5 - 12.

Any suggestions?

Thank you

[giakoum]

if your background is white, color the line white for months 5-12

[mazacini]

Hi ioannis.

Thank you for your response.

Can you tell me how to do this please?

Thanks

Joe

[giakoum]

An if statement is the easy solution :

if(month>5,white(), blue())

[ogster1974]

In appearance go to presentation and show missing values as gaps.

[JonnyPoole]

When you select the line chart, on the right side select 'presentation' -> what do you have for 'missing values' . Is it set to 'show as gaps' ?

[giakoum]

so sorry, I replied believing this was QlikView not QlikSense...

my apologies.

[#8 · Accepted solution]

you could try adding this as a measure:

IF(SUM(MeasureA),SUM(MeasureA))

which evaluates as: if there is a sum for MeasureA, show it, otherwise show nothing

if you wanted MeasureB to show as part of the same line (instead of nothing), you could use:

IF(SUM(MeasureA),SUM(MeasureA),Sum(MeasureB))

[JonnyPoole]

if the 'Show gaps' setting doesn't work, here is an alternative similar to the last suggestion:

Change measures to conditionally > 0 as follows

Sum(Costs)  -->   if( Sum(Costs)>0,Sum(Costs))

Sum(Sales)  -->   if( Sum(Sales)>0,Sum(Sales))

This will replace missing values that evaluate as zeroes with true missing values.  See top as the default setting, bottom with the conditional values. Notice the change from 0 to - (null) and the effect it has on the line chart which i think is what you want

The data i used to create this was as follow. Note 2014 is missing in the 2nd load for Costs.

Data:

load * inline [

Year,Revenue

2016,90

2015,100

2014,75

2013,80

2012,60];

Data2:

load * inline [

Year,Costs

2016,60

2015,50

2013,30

2012,25];

[mazacini]

Thanks Everybody for your responses, and thanks, Andrew and Jonathan especially - your solution worked perfectly!