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

# How can I calculate on only null values?

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?

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?

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?

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