topic Re: Hide null values, but show zero values in chart in QlikView

Hide null values, but show zero values in chart

farinaznz — Sat, 16 Nov 2024 19:18:30 GMT

I have a chart like this:

I want to keep the green, yellow, and pink lines as they are because their values are NULL before '01', '04', and '07', respectively. (the dimension ordering is correct! please don't mind the irregularity) but I want to show that the red line becomes zero after '11' and continues being zero!

"Suppress when value is null" and "Suppress zero values" are checked. when I uncheck "Suppress zero values" I get this result:

so the problem is that QV treats the NULL values as Zeros. How can I prevent this from happening? How can I show when the red line (with actual zero values in the data) is zero but not show zeros when the values of other lines (with NULL values in the data) are missing?

Re: Hide null values, but show zero values in chart

gavinlaird — Tue, 14 Jan 2020 16:30:17 GMT

You could make the line transparent when the value is null. Uncheck "Suppress zero values", then make the line color = if(NOT IsNull(<expression>), argb(0,0,0,0)).

Re: Hide null values, but show zero values in chart

Vegar — Tue, 14 Jan 2020 19:04:53 GMT

Qlik treats null as null, my guess is that you have created a zero value. If you are using aggregation functions then the aggregation will ingnore the null values. You will see that SUM(Null()) = 0.

To get around this problem you could try to write your expression like this:

If( NumericCount(Field), Sum(Field), NULL())

You can also read Henric_Cronström answer in an earlier post: Re: Set analysis returns 0, for null records

Re: Hide null values, but show zero values in chart

farinaznz — Sun, 19 Jan 2020 06:00:34 GMT

You were absolutely right. I was using some aggregation and therefore turning values into zeros. Thank you.