Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
rbartley
Valued Contributor

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

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.

 

 

Tags (2)
1 Solution

Accepted Solutions
rbartley
Valued Contributor

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

I found a way of achieving this by converting the value to text:

If(Value=0,Text('0'),Text(Value))

Display Zero and Null using Text function Pivot Table.PNG

The pivot table now shows nulls and zeros, but hides 'empty' columns.

12 Replies
thiago_justen
Valued Contributor III

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

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

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
rbartley
Valued Contributor

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

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
Valued Contributor III

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

Let's think about it...

 

What about doing this? 

"Uncheck "Include Null Values"

Capturar.PNG

Cheers

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
rbartley
Valued Contributor

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

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
Contributor III

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

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
Valued Contributor

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

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
Contributor III

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

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
Valued Contributor

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

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

 

Summing Nulls Issue.PNG

tomovangel
Contributor III

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

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