Is there a formula or trick in Apptio that replaces NULL values in a raw data set with a zero, for numeric columns?

The logic for several formulas in our model check whether a value is "0" is not. NULL values are being evaluated as non-zero values and this is causing unexpected results with the formulas.

With the number of data sets uploaded into our model each month, we would like to avoid the validation steps required to  ensure each numeric value does not contain NULL values.

Test and see if "=NumberFormat(ColumnName,"0000000")" would work.  Maybe alter to show as =NumberFormat(ColumnName,"0,000.00") if dealing with dollars. 

Simple, yet extremely effective! Thanks!

We're are starting to discuss processes and governance around our TBM discipline and we're trying to eliminate as many touch points (to people or data) as possible. This helps a lot.

Thanks again

Happy to help and glad it worked out!  We have had some issues with project numbers coming in without leading zeroes and that has helped us, too.    Building in logic to catch those 'gotchas' is always easier than crawling through the data every month for sure!

If you want to alter the original column so that nulls are replaced with zeros, and all other values are left alone, you can override with the following formula.

=If($_=null,0,$_)

Getting an error....

"Cannot find column named "null"" lol

Try

=if($_="",0,$_)

That works, but is that truly NULL? What about numeric values?

Not sure I'm following your question?

NULL is NULL no matter what the datatype. Most programming languages have a constant or keyword for NULL, because "" is an empty string type, not exactly NULL. 

My question is, would "" also match NULL for numeric values (not string or text)?

Seems to work for me, but I'm not positive I'm actually checking for NULL. 

Should work for both text and numeric!

NULL in this case is BLANK really - so if you're looking for zero as well, would add that to your logic.  So what Justin had earlier of =if($_="" OR $_=0,0,$_) but add the extra orange text to it.  There are some that may appear as '0', but are actually just >0 and <1..

Excellent tip!