I have created a pivot table and have a mix of positive values, nulls and zeros in the data columns. I would like to be able to display the following:
1) For columns where all values are null, do not display
2) For columns with non-null values display nulls as '-', 0 as 0 and positive values as 0 000 00.##.
I tried using the measure formula: if(Not IsNull(Value),Value), but this still displays the columns where all records are null if I check the Add-ons/Data handling/Include zero values and if I uncheck it, the 0 values disappear.
Thanks in advance.
Solved! Go to Solution.
I found a way of achieving this by converting the value to text:
The pivot table now shows nulls and zeros, but hides 'empty' columns.
You should threat null values into load script. Just like this:
If(Not IsNull(YOURFIELD),YOURFIELD,null()) as YOURNEWFIELD
Also, you could do this:
Considering that your filed is a numeric one, Alt() will replace null values to 0.
I don't see how this will help. I already have null values in the data. Unless I'm mistaken, the solution has to be at the visualization level.
Let's think about it...
What about doing this?
"Uncheck "Include Null Values"
Unfortunately, that's already unchecked for the Year column and all Years have a value, it's the measure or formatting of the measure that needs to be modified.
I had the following scenario, in which I used the following logic:
Lets say we have Customer in dimension and sum(sales) in expression.
Now Whenever a Customer, doesn't have sales it shows 0 or -, so I want to restrict this.
I did it using
basically this is the same expression, but if it is null, it will assign -1 to it.
And then on the dimension I make a limitation to show only values that are greater or equal to 0.
Hi, thanks for responding, but I don't think that will work either. I have tried setting the formula of the measure to
and restriction on the year column in the pivot table to Exact Value >0 , but then the 0 values are also excluded. If I then check the "Include zero values" box for the measure, all of the empty years are displayed again.
I can get the 0 records to display if I convert them using:
if(Value=0,'*0',Value), but then 0 is displayed as *0 instead of 0.
I have tried using the ascii value for 0 (Chr(48)) instead of 0, but this is also removed unless I check the "Include zero values" checkbox.
It would be far more useful to have another checkbox "Include null values" for measures as they do for dimensions rather than just a "Include zero values" checkbox.
well in the limitation on the year column you should have this sign >= which means greater than or equal to, and if you type 0, it should give you all 0 values, + all positive values.
The null values will be with -1 sign due to the expression, so they will be removed from your table 🙂
Tell me if it works
Hi, the issue is that Qlik Sense evaluates Sum(Value) to 0 where Value is Null. So, it never assigns a -1. This is rather odd as summing nulls should result in a result of null. You can see the result in Qlik below, where the first column shows the actual value and the second shows Sum(Value).
use this formula
This should make all null values to be -1, and then you can use the limitations of the dimension..
I don't have another idea..