Missing Data

When data is imported from various data sources, missing values can be present in databases and implicitly in queries. As a result, you must take note as to how missing values are referenced in forms in CDM.

Missing values can be referenced in forms as empty cells or cells containing zeros, depending on the data that must be reported. For example, for OLAP queries (TM1® or Essbase), when you create these types of data queries, you can select how missing values are handled through the Display missing data as {_} option.

If missing values are imported as 0 or empty cells and you do not want them to be reported, you can use an Excel formula to display empty cells in forms instead of zero cells. Type a formula to return empty, for example: IF(logical_test, value_if_true, [value_if_false]), IF(Database!F10="","",Database!F10), or IF(Database!F10=0,"",Database!F10).

Similarly, if missing values from data sources are imported as dashes or null values, you must manipulate those using Excel formulas to return empty (not reported) or zero (reported) cells.