14 Replies Latest reply: Apr 25, 2017 1:21 PM by Robin Hausdörfer RSS

    Distinguishing missing values from null values in if clauses

    Rubens Rodrigues

      Hi folks,

       

      I have the following scenario:

       

      DimensionYear1Year2Year3
      Company 11,000.001,000.00(division by zero result)
      Company 22,000.001,000.00Missing value
      Company 35,000.003,000.00Missing Value

       

      What I want is to replace null values by 'no Revenue', but nothing has to happen with missing values columns.

       

      I've tried:

       

      Alt(

           sum(profit) / sum(revenue), 'no Revenue')

       

      Also:

       

      Alt(

           If(IsNull(sum(revenue) = 0,

                sum(profit) / sum(revenue), 0), 'no Revenue')

       

       

      However, the formula above also replaces missing values by 'no Revenue'.

       

      Could you help me with this?

       

      Thanks,

       

      Rubens Rodrigues