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.
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:
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.
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:
