Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

rmmetcalf
New Contributor III

Ignore NULL values on chart

How do I exclude dimension values that have a NULL measure?  For example, in my bar chart I have employees that do not have data (so their measure value is NULL), but they are still showing up on my chart.  How do I remove them?

When I remove the check from "Include zero values" from the Data Handling menu not only does it remove dimensions with a NULL measure it also removes dimensions with a value of zero.  I want to keep the zero values, but get rid of the NULL values.

1 Solution

Accepted Solutions
rmmetcalf
New Contributor III

Re: Ignore NULL values on chart

I created an Employee dimension in Master Items with the following expression in the Field: Aggr(If($(vMetric)>=0,EmployeeName),EmployeeName).  This formula says if the metric is not null then return the employee.  Essentially, it gives me a dimension with the employees that have the metric.

Then I put this Master Item dimension in the dimension of my chart.  So far, it seems be working (only showing operators who have data in the chart).

5 Replies
MVP
MVP

Re: Ignore NULL values on chart

May be make 0 to be 0.00000001 something like this

If(Avg(Measure) = 0, 0.00000001, Sum(Measure))

Now when you will uncheck 'Include zero values', 0s will stay as they are 0.00000001 now rather than 0s

rmmetcalf
New Contributor III

Re: Ignore NULL values on chart

I'd rather not have a work around solution.  Especially since there is the ability to export data.  I don't want to show 0.00000001 when the number ahould be 0.

MVP
MVP

Re: Ignore NULL values on chart

The other option is to calculated dimension.... I can't think of a 3rd way to do this

rmmetcalf
New Contributor III

Re: Ignore NULL values on chart

I created an Employee dimension in Master Items with the following expression in the Field: Aggr(If($(vMetric)>=0,EmployeeName),EmployeeName).  This formula says if the metric is not null then return the employee.  Essentially, it gives me a dimension with the employees that have the metric.

Then I put this Master Item dimension in the dimension of my chart.  So far, it seems be working (only showing operators who have data in the chart).

MVP
MVP

Re: Ignore NULL values on chart

That is a calculated dimension