Showing zero values but not null values using only measurement

[mariewolfschmidt]

Hi there,

I am having a problem with showing zero values in my table but not null values. 

I have an if statement before the aggregation, which causes the null values to show up. But I need to have the if conditions to showcase my app the right way. 

BTW the code below is only the first part of the if statement. There is more - basically the same calculation. 

This is what I would like the table to look like:

And this is what it looks like

If I deselect "Show zero values", I also lose the zero values, which I need for a chart. If it is selected,  some unnecessary dimension values show up in my charts. 

Is there a way for Qlik to better distinguish between zero and null in this case?

I would like for all of the conditions to stay in measurement since it would be a large amount of work to do it in all dimensions since I have other sheets that work in the same way, but with different dimensions. 

[Or]

It looks like all of the problem cases are when the quarter is -1, so perhaps you could work around this by using a calculated dimension such as:

if(Quarter<> -1, Quarter)

You could then apply "Hide null values" to the dimension and that would result in hiding all lines where the quarter is -1.

[mariewolfschmidt]

Thank you for the reply. It is more than just Quarter = -1. It is for all my conditions within the sum. So for the rows where RowNationality is not 1, it shows the row with a null value. The same goes for CourseProvider_ID and so on. 

I.e. here where Nationality is 'Unknown' and not 'Other'. And the graduationyear is '2020' and not vLatestUnemploymentYear (which is 2019).

[Or]

The same logic on using calculated dimensions would apply regardless - if you can set up a dimension that will return the necessary value when conditions apply, and return null otherwise, you can filter based on that dimension. You can even use aggr() if you need to check aggregations to get it done.

Another option to consider is to add a very small number to each value - 0.0000000000001 or so. This will prevent it from being read as a zero value, but it won't really impact calculations and it assuming you only display a few decimal places, it won't be visible.