topic Re: How to display missing values as gap in QlikView

How to display missing values as gap

Wed, 25 Nov 2020 16:16:04 GMT

Hello

I have a data (attached as tes.xlsx) with two columns: date and volume. There are missing values in the volume (blank cells) but no missing in the date column. My goal is to reproduce a plot similar to attached test.png file that shows the gap in volume (from roughly 2006 to 2012). I have tried a couple of ways in expression and also played with "suppress zero-values", "suppress missing" options in Presentation but all I got is the Doc1.pdf plot which annoyingly connects the two endpoints as a line.

Appreciate any help or words of wisdom you might share.

Thanks!

Re: How to display missing values as gap

swuehl — Tue, 22 Nov 2016 16:45:47 GMT

Maybe just change the color of the line:

Re: How to display missing values as gap

Tue, 22 Nov 2016 18:54:29 GMT

Stefan

Have been trying to figure out what exactly you did in your qv file that I missed but I am unable to find out. Could you comment more on what you mean by changing the color of the line?

Re: How to display missing values as gap

MK9885 — Tue, 22 Nov 2016 19:22:01 GMT

He changed the background color for the expression

If(Sum([Monthly Volume]), color(1),white() ) <<< Use this in your background color.

Re: How to display missing values as gap

Tue, 22 Nov 2016 19:40:43 GMT

Found it but the last data point before the gap drops falsely to zero which is not reflected in data. That said, the last point before the gap is Feb-2206 with the value of 48 but from the drawing, it goes down to zero before the gap which is not correct.

Re: How to display missing values as gap

swuehl — Tue, 22 Nov 2016 19:46:12 GMT

Right, QV is coloring the segment following the data point.

Maybe try

=If(Below([Monthly Volume]) and [Monthly Volume], color(1),white() )

edit: and maybe use another expression to draw dots to indicate singular events.

See attached

Re: How to display missing values as gap

MK9885 — Tue, 22 Nov 2016 19:55:20 GMT

It's not showing 0.

Convert the chart to Bar and you'll see the bar for Feb 2006

Or change the sorting order to Y Value Decending....

It's not clearly visible ad the value is very low and it is a line chart. (that's my guess) but you've the value and it is not 0 (even when you select the filters)