2 Replies Latest reply on Jan 2, 2014 8:10 AM by Joshua Milligan

    sum of measures returns null

    Tara Carter

      Hi there

       

      I need to sum up three measures: Retweets, Comments, Likes.

      Data Type is Float, and all measures have some cells blank.

      Retweets+Comments got numbers,

      Retweets+Comments+Likes got Null.

       

      I've tried to recreate table in SQL but no good. Wondering what could be causing this issue? Any help appreciated!

        • 1. Re: sum of measures returns null
          Matt Lutton

          Have you tried returning 0s where there are blank cells?  The ZN() function may help with this.

          ZN(expression)

          Returns the expression if it is not null, otherwise returns zero. Use this function to use zero values instead of null values. ZN([Profit]) = [Profit]

           

          If this doesn't help, you can post a packaged workbook with some sample data that others can use to evaluate the problem.  Cheers.

          • 2. Re: sum of measures returns null
            Joshua Milligan


            Tara,

             

            Matthew is correct.  To clarify a little, you will get a NULL if you attempt to add it to any other value.  In your example above, it is likely that Likes is NULL (at least in some cases), so try something like:

             

            ZN(Retweets) + ZN(Comments) + ZN(Likes)

             

            That way if it were originally:

            30 + 50 + NULL which results in NULL

            it would be calculated as

            30 + 50 + 0 = 80

             

            Regards,

            Joshua

            1 of 1 people found this helpful