Hide pivot table columns where all values are null but show 0s

[rbartley]

Hi everyone,

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.

Any ideas?

Thanks in advance.

[Thiago_Justen_]

Hey guy,

You should threat null values into load script. Just like this:

If(Not IsNull(YOURFIELD),YOURFIELD,null()) as YOURNEWFIELD

Also, you could do this:

Alt(YOURFIELD,0)

Considering that your filed is a numeric one, Alt() will replace null values to 0.

Cheers

[rbartley]

Hi Thiago,

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.

[Thiago_Justen_]

Let's think about it...

What about doing this? 

"Uncheck "Include Null Values"

Cheers

[rbartley]

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.

[tomovangel]

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 

If(isnull(sum(sales),-1,sum(sales))

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. 

[rbartley]

Hi, thanks for responding, but I don't think that will work either.  I have tried setting the formula of the measure to

if(IsNull(Sum(Value)),-1,Sum(Value))

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.  

[tomovangel]

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

[rbartley]

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

[tomovangel]

use this formula

if(isnull(value),-1,sum(value))

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