SAP Knowledge Base Article - Public

3014619 - How to check for a NULL in a table and return a 0 within an SAP Analytics Cloud Story.

Symptom

  • When a table has columns that contain a NULL value it is needed to check for these values and convert them to a specific value for use in other calculations (Example: Add/Subtract) to ensure that the expected results are generated.
  • How can a column be checked if it is NULL and then ensure that a specific value is used instead if it is NULL?

Environment

  • SAP Analytics Cloud (SAC) 

Reproducing the Issue

  • Have a table within an SAP Analytics Cloud Story that contains some NULL values which causes calculations using Add or Subract to return NULL as the result.

Cause

  • NULL or "Blank" or "Empty" cells are valid data entries, but these cells are not equivalent to the value 0.
  • NULL Values cause the Add or Subtract functions to generate a NULL result.

Resolution

  • Create a formula in the table to check for the NULL
    • equal(D3,null)
      -> will return T (true) if it is NULL or F (False) if it is not NULL
  • Add an If statement to the table formula to specify what value to use if the NULL is found or to use the actual value if it is not NULL.
    • IF(equal(D3,null),0,D3)
      -> Logic explanation: If the D3 column is NULL then replace with a 0 otherwise use the existing D3 column value.
  • Reference the Conditional Operators section of the 'All Formulas and Calculations' page for SAP Analytics Cloud.
  • Aftwards use this cell formula in other formulas to ensure that the expected results are seen for the calculations.

See Also


Your feedback is important to help us improve our knowledge base.
Please rate how useful you found this article by using the star rating feature at the beginning of this article.
Thank you.

 

Keywords

Null Value, Function, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics , KBA , LOD-ANA-TAB , Table , How To

Product

SAP Analytics Cloud 1.0