Logical Functions in Calculated Fields

In this article let’s look at some logical functions available in Functions area while creating a calculated field.

List of Logical Functions:

1. CASE
2. IF
3. IFNULL
4. IFF
5. ISDATE
6. ISNULL
7. ZN

Source: Dataflair

CASE:

CASE [expression] WHEN value1 THEN return1 WHEN value2 THEN return2 … ELSE [default value return] END

Eg: CASE [Country] WHEN “United States” THEN “US” WHEN “United Kingdom” THEN “UK” ELSE “World” END

IF:

IF [Condition] THEN value END / IF [condition] THEN value ELSE else END

Eg : IF [New users > Old users ] THEN “Improvement” ELSE “No improvement” END.

IFNULL:

IFNULL [Condition,0]

Eg: IFNULL [Activeusers , 0]

The first variable is the “check” and the second variable is “Value”. In the above example, the function checks if there are any null values for the “Activeusers” field. If there are any null values we want the null to be replaced by “0” in that particular field.

IIF:

IIF(test, then, else, [unknown])

Eg: IF ([Active users]>100, “GOOD”, “POOR”).

If Active users counts are greater than 100, It's Good or else the condition is Poor. The reason to add [unknown] is to set a default value just in case the condition fails to give valid input.

ISDATE:

ISDATE(ISODATE)

Eg: ISDATE function is to check whether the ISO date given as a string is TRUE OR FALSE ( A valid date or not )

Eg: ISDATE (1562312644) is a valid date, hence “TRUE”

ISNULL:

ISNULL([country])

ISNULL function simply validates whether the expression is NULL (True) or not (False).

ZN:

ZN(Expression)

ZN(Expression) checks whether the given expression value is null or not. If it is null, it returns a “0”