Calculated Columns using IF and IN operators in Zoho Reports

Filed under #SoIDontForgetHowIDidThis, #Zoho

In Zoho Reports you can’t add lookup or standard columns to data sourced from Zoho CRM. You can however add calculated columns, which is great if you want to transform a list of usernames into a department for future filtering or grouping.

The syntax for this isn’t well documented, and it took a wee bit of fiddling to figure it out. We combine the IF function with the IN operator where:

IF is a function defined as:

if(expr1, expr2, expr3)

Returns expr2 if expr1 is true else it returns expr3

Example: if(5 > 10,100,50) = 50

IN is a list operator defined as: 

<expression1> in <expression2>

It’s also useful to know that lists are enclosed in round brackets (), string literals are enclosed in single quotes ‘’ and column references are  enclosed in double quotes “”.

E.g, to return the department name of a user based on the potential owner, the formula would be:

IF("Potential Owner" IN ('Anthony','Seth', 'Chris'), ‘Technology Infrastructure' , ‘Software Development’)

Which will result in a list similar too:

  • Seth – Technology Infrastructure
  • Bob – Software Development
  • Carol – Software Development
  • Anthony – Technology Infrastructure

For a more explicit result we can nest the IF function, which will be familiar to many Excel users:

IF("Potential Owner" IN ('Anthony','Seth', 'Chris'), 'Technology Infrastructure' , IF("Potential Owner" IN ('Phillip’,'Bob'), 'Software Development' , 'Other’))

Will result in:

  • Seth – Technology Infrastructure
  • Bob – Software Development
  • Carol – Other
  • Anthony – Technology Infrastructure

As Carol isn’t explicitly defined as being in the TI or SD departments, this is more correct.

Advertisements