Qliksense pivot table background colour when null value

[Newbie_QLIK]

Hi Qlik Community,

I hope this message finds you well.

I’ve learned that Qlik Sense doesn’t apply background color to cells with null values because it doesn’t insert anything into those cells. In a previous discussion (link), lblumenfeld suggested placing a space in the cell for null values to ensure the background color is applied (e.g., If(Not IsNull([Your Field]), [Your Field], ' ')).

I tried this approach, but it didn’t work. Can anyone help identify the problem in the following expression?

=if(not isNull(
pick(Dim,
count(Saleproduct),
count(Saleproduct)/(200/5/12),
count(Saleproduct)/(200/12),
rangesum(count(Saleproduct))
)
),
pick(Dim,
count(Saleproduct),
count(Saleproduct)/(200/5/12),
count(Saleproduct)/(200/12),
rangesum(count(Saleproduct))
),
""
)

Thank you for reading!

If you have any alternative methods to achieve this, please feel free to share.

Best,

Newbie_Qlik

[datagrrl]

I think you might need single quotes and not double in the last part (default value).

[Newbie_QLIK]

Thank you for the quick reply.

Indeed, the error message is gone after I replaced the double quotes with single ones. However, My table looks the same (i.e., null value cell background is still grey). 

Do you know why it would do that? 

Thank you again for taking your time to answer.

Have a great day.

Best,

[MatheusC]

@Newbie_QLIK 
I believe this is not the same scenario as the shared link.

I don't see your visualization built, but I imagine you have missing data in your pivot table due to the data you are crossing.

That said, my suggested alternative is the new pivot table, under the Custom Objects > “Qlik Visualization bundle” tab. It is now possible to make this customization in the chart style tab.

Note: Depending on the Qlik version used, this may  not be available.

- Matheus

[TauseefKhan]

@Newbie_QLIK 
Check with Alt() to replace nulls with a space:

=if(not isNull(
pick(Dim,
count(Saleproduct),
count(Saleproduct)/(200/5/12),
count(Saleproduct)/(200/12),
rangesum(count(Saleproduct))
)
),
pick(Dim,
count(Saleproduct),
count(Saleproduct)/(200/5/12),
count(Saleproduct)/(200/12),
rangesum(count(Saleproduct))
),
Alt(pick(Dim,
count(Saleproduct),
count(Saleproduct)/(200/5/12),
count(Saleproduct)/(200/12),
rangesum(count(Saleproduct))
), ' ')
)


***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[Newbie_QLIK]

Thank you for your answer.

Unfortunately, it did not work. I still see the grey empty cells as below. thank you again for your time 🙂

[Newbie_QLIK]

Thank you for your reply!

My version does not have pivot table yet. I only have P&L pivot table.

Hope it would be available soon.

Have a good weekend

Best,

[datagrrl]

What version are you on? I am on Feb 2024 and I do not even have to use the custom pivot table to get this to work.

=if(not isNull(
pick(Dim,
count(Saleproduct),
count(Saleproduct)/(200/5/12),
count(Saleproduct)/(200/12),
rangesum(count(Saleproduct))
)
),
pick(Dim,
count(Saleproduct),
count(Saleproduct)/(200/5/12),
count(Saleproduct)/(200/12),
rangesum(count(Saleproduct))
),
''
)

How are you setting your background color? Do you have an expression for that?

I took a guess at what your data looks like. 

In February 2024 it looks like this. The top version is the expression above.

[Newbie_QLIK]

HI datagrrl,

Thank you for your reply. I am on November 2022.

My background colour expression is as follow: 

=pick(Dim,
If(dimensionality() = 0, rgb(255,255,100), If(dimensionality() = 1, rgb(255,255,204), if(dimensionality() = 2, rgb(255,250,240), white()))),
If(dimensionality() = 0, rgb(255,255,100), If(dimensionality() = 1, rgb(255,255,204), if(dimensionality() = 2, rgb(255,250,240), white()))),
If(dimensionality() = 0, rgb(255,255,100), If(dimensionality() = 1, rgb(255,255,204), if(dimensionality() = 2, rgb(255,250,240), white()))),
rgb(188,188,155))

Thank you again for your help!

Best,

[MatheusC]

In response to Newbie_QLIK

According to information about the solution presented, the new pivot table is available from the February 2024 version, for it to be available it is necessary to update to this version or later

- Matheus