Tableau 8.2 calculated field with If thens

Question

I am working on creating some calculated fields in Tableau 8.2.

The data for the "test2" field is imported from Access. They can be numbers or "Null" in text.

I also have fields "test1" and "minimum" and "maximum". "test1", "minimum" and "maximum" are only numbers.

I would like to do an calculated field with an if statement.

The name of the calculated field is "answer".

I would like to do the following:

1) If "test2" is not "Null" and "test2" < "minimum" then calculate "minimum" - "test2".

(I was using the syntax IIF and != for not equal, but it did not like it because "Null" is a string value)

2) else if "test2" is not "Null" and "test1" < "minimum" then calculate "minimum" - "test1"

How would I go about doing this? Please advise.

Answer 1

IF (NOT ISNULL([test2])) AND [test2] < [minimum]
THEN [minimum] - [test2]
ELSEIF (NOT ISNULL([test2])) AND [test1] < [minimum]
THEN [minimum] - [test1]
END

Not so hard if you know ISNULL function

Answer 2

Testing for null is redundant (unnecessary) in this case.

An if condition that references a field with a null value evaluates to false. This is different than, say, Java programming. You don't have to test fields for null values before referencing them in most cases. Helpfully, aggregation functions like min(), max(), sum(), count() etc ignore null values altogether.

Unnecessary null tests make formulas hard to read and can easily hide typos. Assuming you meant the test1 instead of test2 in your second test above, then your calculated field need only state:

if test2 < minimum then
  minimum - test2
elseif test1 < minimum then
  minimum - test1
end

Inox is correct that when you do need to explicitly test for null values, the function to use is isnull(), or in some cases ifnull() or zn().