Conditionally hiding points on a chart

[#1]

Hi,

I've got some data generated via an SQL query in the following structure:

LOCATION	HOUR	ACTUAL	FORECAST
A	0	22	20
A	1	24	22
A	2	12	13
A	3	0	4
A	4	6	0
A	5	0	7
B	0	10	6
B	1	12	7
B	2	NULL	18
B	3	NULL	0
B	4	NULL	22
B	5	NULL

With HOUR as the dimension, I'm graphing ACTUAL and FORECAST as the expressions (SUM) on the same graph, FORECAST is a bar chart, ACTUAL is an overlayed line.  A and B are set as selectable on the UI.

As you might have guessed, FORECAST is a prediction of ACTUAL.  If it is hour 1, there can exist a FORECAST for hour 2 and upwards, but there can not be an ACTUAL, hence the NULL values.

Qlikview seems to treat NULL values as zeros, so when the line for ACTUAL is graphed, the NULL values are graphed as a horizontal line along the x axis - I want to be able to hide these values (prevent them from being plotted).  I can suppress zero values, which hides the NULL values ok, but that also hides legitimate zero values for both ACTUAL and FORECAST, which need to be plotted.

Using ONLY VALUE() as the expression definition instead of SUM() successfully hides the NULL values for each of the locations individually, but when I have A and B both selected, it doesn't sum the figures for both locations.

Basically, I want to tell Qlikview to ignore NULL expression values, but keep the dimension values when plotting. (So, plot empty y values for some x values).

I guess you could say I'm trying to prevent interpolation.

Hope this makes sense and someone can shed some light!

Thanks

[jagan]

Hi,

Try this from help file

NullInterpret

The defined symbol will when it occurs in a text file, Excel file or an inline statement be interpreted as NULL. A user-defined variable.

Example:

set NullInterpret=' ';

set NullInterpret =;

will NOT return null values for blank values in Excel (but it will for a csv text file)

set NullInterpret ='';

will return null values for blank values in Excel (but will NOT for a csv text files)                   

In your case use

SET NullInterpret = 'NULL';

Hope it helps you.

Regards,

Jagan.

[#3]

Hi Tom,

   Please find the attached Document, It may help you for the solution.

Santhosh G

[#4]

Hi Santhos

Thanks for the quick response - I see what you've done there, the problem is your graph still plots them as zeros - I don't want them there at all.  Basically I'm trying to have a broken line where the values are NULL.  Is that possible, or is going to be a case of trying to overlay 2 separate graphs over eachother?

Thanks

[#5]

Hi Tom,

Please find the other work arround. May be it helps.

[jagan]

Hi,

Broken line is not possible in Qlikview.

Regards,

Jagan.

[#7]

Hi Santhosh,

That appears to be what I need - I can't see a difference between the settings on the 2 files though - how did you prevent the ACTUAL point for hour 5 from plotting?

Tom

[jagan]

HI,

Select Suppress zero values option in Presentation tab, you will get this.

Regards,

Jagan.

[#9]

Ah, I see - the problem there is that I still need legitimate zeros to be included.

Let's approach it a different way - all the NULL values that I don't want to plot will appear after a certain dimension value (i.e. after current hour) - is there a way to only plot the first 20 points of one expression, but 40 of another on the same axes?