topic Chart showing Zero but NOT null in QlikView

Chart showing Zero but NOT null

Thu, 26 Mar 2015 16:26:48 GMT

I have a data set which has some zero values but also some null values. What I need to do is show the zero values but not the null values and am not sure how to achieve that. I saw another post using the P() function but couldn't get that to work for me (may be ignorance on my part).

The chart has actual values, average actual values (a running six-week average - less until it hits six weeks of actual data) and then predicted values going out about two months. All these values come from the database and are not calculated within Qlikview.

The actual values have some zero values I want to show but if I don't suppress zero values, it also extends the actual portion of the chart into the predicted area and likewise, the predicted portion of the chart overlaps with the actual area.

I've attached a sample file with data and with the same chart twice - once with zero's suppressed and once with them not suppressed. Neither is what I need but the zero suppressed is closer.

Thanks for any help you can provide.

Re: Chart showing Zero but NOT null

Thu, 26 Mar 2015 17:32:39 GMT

I've got a kludge type workaround but would like something better.

I've changed my query which brings the data in to check for 0 values and then assign them .001 as a value instead. As I'm dealing with whole numbers (bigint), this works in this instance giving me the look a desire for the chart.

However, that is not an option in all cases so I'd still like to know how I can achieve the functionality without altering the data.

Thanks.

Mark.

Re: Chart showing Zero but NOT null

rubenmarin — Thu, 26 Mar 2015 17:50:12 GMT

Hi Mark, Sum(Null()) will give a result of zero, so if you want to retrieve null or zero you can ask first if there is any value, if any value exist then do the sum.

In your Zero Not Supressed chart you can use this expressions:

If(Count({<Metric = {'Accepted Tests'}>} Tests), Sum({<Metric = {'Accepted Tests'}>} Tests))

If(Count({<Metric = {'Avg Accepted'}>} Tests), Sum({<Metric = {'Avg Accepted'}>} Tests))

If(Count({<Metric = {'Predicted Accepted'}>} Tests), Sum({<Metric = {'Predicted Accepted'}>} Tests))

Re: Chart showing Zero but NOT null

Thu, 26 Mar 2015 18:31:02 GMT

Perfect! Thank you!