Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Nulls Causing Conditional Formatting to Fail

In some of our table objects we're applying conditional formatting using a background color expression.   Specifically, we're applying color banding to the rows of the table to make things easier to see.  We've noticed that for cells that are null, the formatting doesn't work.

Here's the formula we're using: 

if(RowNo(total)>=1 ,if(mod(Rowno(total),2)=0,RGB(184,204,228),RGB(220,230,241)),if(RowNo(total)=0,RGB(0,60,100)))

This works for everything but null cells.  A screen shot of the issue is below.  These nulls aren't in the source data - they are "app-side" nulls caused by a lack of data points for certain combinations of fields/tables.  So I don't think we can handle this in the load script. 

Any ideas on how we might tweak our above formula to handle these nulls?

Banding Issue.PNG

Thanks,
Steve

Labels (2)
6 Replies
Highlighted
Creator III
Creator III

Hey,

is this RateM... field calculated or just a field loaded from database? 

Highlighted
Contributor III
Contributor III

It's a field loaded from the database.

Highlighted
Creator III
Creator III

Okey, so in the column just use this if: 

=if(IsNull([Your Field]),'-',[Your Field]) 

this will allow you to color field with '-' in it same as other fields.

Highlighted
Contributor III
Contributor III

Unfortunately, that won't work in our case.  The nulls in this particular field aren't in the source data - they're being caused on the app side.  For nulls in fields/cells in the source data, we typically try to handle them in the load script.  However, there are also nulls that show up in the app, due to combinations of products/dates/etc. that didn't even exist in the source data.  For these app-side nulls, I've never found a good solution on how to handle them.

Highlighted
Creator III
Creator III

do you use where exist when you load tables?

Highlighted
Creator III
Creator III

This generated NULL value is being called Missing value and there is a lot about trying to color it in this community. 

I did go through threads but didn't find solution.