Replace missing or Null values

 

Replace missing or Null values

Option 1: Use ZN

1. Select Analysis > Create Calculated Field
2. In the Calculated Field dialog box that opens, do the following, and then click OK:
   1. Name the calculated field. In this example, the calculated field is named "Replace empty cells with zero (opt 1)"
   2. In the formula field, create a calculation similar to the following:

      ZN(SUM([Amount])) 

   • The ZN() function will replace any NULL values with zero
3. Create a calculated field with a name like " Replace empty cells with last value (opt 1) " with a calculation similar to the following:
   • IFNULL(
         SUM([Amount]),
         PREVIOUS_VALUE(0)
     )
   • IFNULL(..., PREVIOUS_VALUE(0)) will replace any NULL values with the last value of this calculation, which creates a running last value.
   • Depending on how the view is built, it may be necessary to compute PREVIOUS_VALUE() differently.
4. Replace [Amount] on Text on the Marks card with either [Replace empty cells with zero (opt 1)] or [Replace empty cells with last value (opt 1)] depending on the desired end result
5. (Optional) Customize the values in the view by right-clicking the value and formatting it as desired.