Nulls Causing Conditional Formatting to Fail

[steverosebrook]

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?

Thanks,
Steve

[y_grynechko]

Hey,

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

[steverosebrook]

It's a field loaded from the database.

[y_grynechko]

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.

[steverosebrook]

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.

[y_grynechko]

do you use where exist when you load tables?

[y_grynechko]

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.