Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Alma1234
Contributor III
Contributor III

Paint Columns of Pivot table - Null values problem

Hello,

I have a  pivot table where i need to color all actual columns in gray.

I added an expression under 'Background color' as 

=if(Dimension2='Actual,RGB(221,221,221))​ but the Null values remain White.

Is there a way to change null values in script to a number. i tried ifnull then 0 but it didn't work.

Any idea will be appreciated.

Regards

 
 
 
 

 

1 Solution

Accepted Solutions
Anil_Babu_Samineni

This statement will change non-values to real null values. You can mention as it is like

NullAsValue Dimension2, Dim1, Dim3, Dim4...;

Set NulValue = 'NULL';

Fact1:

Load * From T1;

Fact 2:

Load * From T2;

etc...

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

6 Replies
Anil_Babu_Samineni

Try this way

NullAsValue Dimension2;

Set NullValue = 'NULL';

and, expression should be

=if(Match(Dimension2, 'Actual', 'NULL'),RGB(221,221,221))​

But, in your expression I am not finding anything for null values as color hence define as

=if(Dimension2='Actual' or IsNull(Dimensio2),RGB(221,221,221))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Alma1234
Contributor III
Contributor III
Author

Hi,

Thanks for the answer. 

I have null values for all  dimensions so it is not working. 

If i paint null values than i get gray in the dimensions Budget and Last Month as well.

 

Cheers,

Anil_Babu_Samineni

I feel those are not real null values, Hence i may suggest this way as always

NullAsValue Dimension2, Dim1, Dim3, Dim4...;

Set NullValue = 'NULL';

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Alma1234
Contributor III
Contributor III
Author

You are right. some are Null values and some are missing values.

What do you mean by

NullAsValue Dimension2, Dim1, Dim3, Dim4...;? 

I have 4 dimensions, to write them on script as variable?

 

Anil_Babu_Samineni

This statement will change non-values to real null values. You can mention as it is like

NullAsValue Dimension2, Dim1, Dim3, Dim4...;

Set NulValue = 'NULL';

Fact1:

Load * From T1;

Fact 2:

Load * From T2;

etc...

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Alma1234
Contributor III
Contributor III
Author

Hi,

You are right. It solved the null values. 

The other rows are non-exist rows, for this i guess i can only fill the rows on script.

Thanks,