3 Replies Latest reply on Mar 23, 2017 5:04 PM by Okechukwu Ossai

    How can I calculate on only null values?

    Angela Blackburn

      I have a field called Franchise that returns 50% of null values. (Not all of my data has a franchise associate with it, but does have an order quantity)

      I only need to calculate what the total quantity of sales is if this field is null.

       

      Like this:

      CASE [Franchise]

      WHEN ISNULL([Franchise])

      THEN TOTAL([QUANTITY/SALES SWAPPER])

      ELSE ([Franchise])

      END

       

      How can I do this?  I have tried IF, and CASE. 

        • 1. Re: How can I calculate on only null values?
          HUSSAIN SHAIK

          Hi Angela,

           

          up to my understanding, you want to calculate Sales where there is no Franchise.

           

          You Can write a formula saying

          calc1-->if isnull(franchise) then sales else 0 end

          then you can take sum of calc1 to get sales of null frachise.

           

          If you can share workbook with example, that will help better to understand your query.

          • 2. Re: How can I calculate on only null values?
            Jamieson Christian

            Angela,

             

            Try rewriting it like this:

             

            IF ISNULL([Franchise])
            THEN TOTAL([QUANTITY/SALES SWAPPER])
            ELSE ([Franchise])
            END
            

             

             

            What was happening in your CASE statement?

             

            A CASE statement compares the field to each value in the WHEN clauses. ISNULL([Franchise]) returns TRUE or FALSE, so your CASE statement essentially checks to see if [Franchise] = TRUE or FALSE (which would always fail, I reckon).

             

            Because you need to use ISNULL() rather than a straight equality operator (which CASE does implicitly), you cannot use a CASE statement. You must use an IF statement instead.

            • 3. Re: How can I calculate on only null values?
              Okechukwu Ossai

              Hi Angela,

               

              From the sample code you provided, it appears you are trying to find the total sales of null franchises. Is this correct?

               

              If yes, you can create a new Franchise field.

               

              Let's call it [Franchise-new]

              IF ISNULL([Franchise] THEN "No Franchise Information"

              ELSE [Franchise]

              END

               

              This will duplicate your Franchise field but will replace all null values with "No Franchise Information"

               

              Using this new Franchise field, go ahead and calculate sales for "No Franchise Information" as you would for any other Franchise.

              This will be equivalent to the sales associated with the null values.

               

              Hope this helps. Let me know how you get on.

               

              Ossai