What are the functions available to add formula columns?

Functions available to add formula columns







Abs

abs(num)

Returns the absolute value of the numeric value.

Parameters
Name
Description

num

Decimal

Parameter must be a numeric value from a column or an expression that returns a number.
Example
Function
Result
abs(-1)
1


Div

div(numerator,denominator)

Performs floating-point division. Returns a decimal value.

Parameters
Name
Description

numerator

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.

denominator

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
div(3,2)
1.5


Add

add(num1,num2)

Returns param1+param2. Performs numeric addition.

Parameters
Name
Description

num1

Decimal

Parameter must be a numeric value or a Number column.

num2

Decimal

Parameter must be a numeric value or a Number column.
Example
Function
Result
add('3', '2')
5



Sub

sub(param1,param2)

Performs numeric subtraction. Returns param1 - param2.

Parameters
Name
Description

param1

Decimal

Parameter must be a numeric value.

param2

Decimal

Parameter must be a numeric value.
Example
Function
Result
sub('3', '2')
1



Mul

mul(param1,param2)

Performs numeric multiplication.

Parameters
Name
Description

param1

Decimal

Parameter must be a numeric value.

param2

Decimal

Parameter must be a numeric value.
Example
Function
Result
mul('3', '2')
6



Avg

avg(list)

Returns the average value in a numeric column or list

Parameters
Name
Description

list

Decimal

Parameter must be a numeric column, or a list of numbers
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
avg(ticket_count);
60



Min

min(num)

Returns the minimum value in a list of numeric values

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column or a list of numeric values
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
min('ticket_count');
30



Max

max(num)

Returns the maximum value from a numeric column

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
max('ticket_count');
90



Max Date

max_date(date)

Returns the maximum date value from a date or a date-time column.

Parameters
Name
Description

date

Datetime

Parameter must be a date column or a date-time column.
Example
Data column
06 Dec 2018
17 Nov 2019
20 Oct 2020
Function
Result
max_date(Data column)
20 Oct 2020



Min Date

min_date(date)

Returns the minimum date value from a date or a date-time column.

Parameters
Name
Description

date

Datetime

Parameter must be a date column or a date-time column.
Example
Data column
06 Dec 2018
17 Nov 2019
20 Oct 2020
Function
Result
min_date(Data column)
06 Dec 2018



Sum

sum(num)

Returns the sum of the values in the given column

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column, or a list of numbers
Example
marks
45
33
56
70
Function
Result
sum(marks)
205



Mode

mode(num)

Returns the most occurring value in the column

Parameters
Name
Description

num

Decimal

Parameter must be a numeric column
Example
ticket_count
30
40
30
60
30
80
90
Function
Result
mode('ticket_count');
30



Count

count(col)

Returns the total number of values in the column. Null values (empty cells) are ignored.

Parameters
Name
Description

col

Text

Parameter must be a column
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
count(ticket_count);
7



Count with Blanks

count_wb(col)

Returns the total number of values in the column including null values (empty cells).

Parameters
Name
Description

col

Text

Parameter must be a column
Example
ticket_count
30
40
50

70
80
90
Function
Result
count_wb(ticket_count);
7



Variance

variance(param)

Returns the variance calculated from values of a group in a number format.

Parameters
Name
Description

param

Decimal

Parameter must be a numeric group of values such as a Number column.
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
variance("ticket_count")
466.66



Standard Deviation

stddev(param)

Returns the standard deviation calculated from the input values.

Parameters
Name
Description

param

Decimal

Parameter must be a numeric value or a Number column.
Example
Score
10
20
30
40
50
60
Function
Result
stddev(Score)
18.70



Binary

bin(param)

Returns the binary representation of a number.

Parameters
Name
Description

param

Number

Parameter must be a numeric value or a numeric column or an expression that returns a number.
Example
Function
Result
bin('111')
1101111



Cube Root

cbrt(param)

Returns the cube root of the number.

Parameters
Name
Description

param

Decimal

Parameter must be a number, or a numeric column or an expression that results in a numeric value.
Example
Function
Result
cbrt('27')
3



Ceiling

ceil(param)

Returns the smallest integer value that is bigger than or equal to a number.

Parameters
Name
Description

param

Decimal

Parameter must be a number, or a numeric column or an expression that results a numeric value.
Example
Function
Result
ceil('25.75')
26



Character

char(param,...)

Returns the ASCII character having the binary equivalent to the parameter. If n is larger than 256 the result is equivalent to char(n % 256).

Parameters
Name
Description

param

Multiple

Parameter must be a number, a numeric column, or an expression that returns a number.
Example
Function
Result
char('65')
A



Concat

concat(params)

Returns the concatenated text of the given parameters. Returns null, if any one of the parameters is null.

Parameters
Name
Description

params

Text

Accepts any number of parameters. Parameters must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
concat("NOW", "HERE")
NOWHERE



Concat with Separator

concat_ws(params)

Returns the concatenated text of the given parameters along with the separator. Returns null, if any one of the parameter is null.

Parameters
Name
Description

params

Text

The first of the params is considered as the separator, using which the rest of the parameters will be concatenated. All parameters must be Text, Text columns, or expressions that returns a Text.
Example
Function
Result
concat_ws('_', 'NOW', 'HERE')
NOW_HERE



Convert

conv(param,from_base,to_base)

This function converts the number from 'from base' to 'to base'.

Parameters
Name
Description

param

Text

Parameter must be a number or a number column, or an expression that returns a number.

from_base

Decimal

The base value to be converted from.

to_base

Decimal

The base value to be converted to.
Example
Function
Result
conv(100, 2, 10)
4



Current Date

current_date()

Returns the current date as a date value. Function accepts no parameters.

Example
Function
Result
current_date()
09/21/2019



Current Timestamp

current_timestamp()

Returns the current timestamp as a date-time value. Function accepts no parameters.

Example
Function
Result
current_timestamp()
09/19/2019 19:18:55



Date Format

date_format(date,format)

Converts the date-timestamp to the format specified in the parameter.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column to change the format.

format

Text

Specifies the format in which the date should be returned by the function.
Example
Data column
2017-06-15
Function
Result
date_format(Data column, "dd MMM, yyyy")
15 June, 2017
Note: If you wish to provide the date input directly into the function as shown below, please use either of these supported formats: “dd MMM, yyyy” or “dd MMM, yyyy HH:mm:ss"

Example function:
date_format(14 June 2017 18:20:56, dd MMM, yyyy HH:mm:ss) 

Function output: 
2017-06-14 18:20:56



Day of Month

day_of_month(date)

Returns the day of month of the date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2009-03-24
Function
Result
day_of_month(Data column)
24


Day of Week
day_of_week(date)

Returns the day of the week for the given date. [1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday]

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
06-12-2018
Function
Result
day_of_week(Data column)
5



Week Day

weekday(date)

Returns the day of the week from the given date

Parameters
Name
Description

date

Datetime

Parameter must be a date column or a date-time column
Example
Data column
06 Dec 2018
Function
Result
weekday(Data column);
3



Day of the Week in name

day_of_week_name(date)

Returns the day of the week (as name) for the given date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
06 Dec 2018
Function
Result
day_of_week_name(Data column)
Thursday



Day of the Year

day_of_year(date)

Returns the day of year (in number) of the date/timestamp.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
25-03-2009
Function
Result
day_of_year(Data column)
84



Decode

decode(param,charset)

Returns the decoded value of the first argument using the second argument character set (UTF-8, ASCII, UTF-10, etc.).

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

charset

Text

Specifies the character set such as UTF-8, ASCII, UTF-10, etc.
Example
Function
Result
decode("ABC", "UTF-8")
ABC



Degrees

degrees(param)

Converts radians to degrees.

Parameters
Name
Description

param

Decimal

Specifies the angle in radians.
Example
Function
Result
degrees(3.141592653589793)
180.0



Encode

encode(param,charset)

Returns the encoded value of the first argument using the second argument character set (utf-8, ascii, utf-10, etc.).

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

charset

Text

Specifies the character set. 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16' are the supported character sets.
Example
Function
Result
encode("ABC", "UTF-8")
ABC



Exponential

exp(param)

Returns the exponential value of the given number. This value is the exponent (e) raised to the power specified as the parameter.

Parameters
Name
Description

param

Decimal

Parameter must be a number or an expression that returns a number.
Example
Function
Result
exp(1)
2.718281828


Floor

floor(param)

Returns the largest integer that is less than or equal to the given parameter.

Parameters
Name
Description

param

Decimal

Parameter must be a number or an expression that returns a number.
Example
Function
Result
floor(-0.1)
-1



Greatest

greatest(params)

Returns the largest value from a group of values.

Parameters
Name
Description

params

Decimal

Supports multiple parameters, The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers.
Example
Function
Result
greatest(10, 9, 2, 4, 3)
10



Hexadecimal

hex(param)

Returns the hexadecimal value of the given parameter.

Parameters
Name
Description

param

Text

Parameter can be any value or a column of Number, Text or Boolean type.
Example
Function
Result
hex(17)
11



Hour

hour(datetime)

Returns the hour component of the timestamp.

Parameters
Name Description

datetime

Datetime

Specifies a Time or a Datetime column.
Example
Data column
2009-03-07 20:18:34:543
Function Result
hour(Data column) 20

If

if(condition,param1,param2)

If the condition is true, then returns param1; otherwise returns param2.

Parameters
Name
Description

condition

Boolean

An expression that returns a Boolean.

param1

Text

Parameter can be any value or a column of any type.

param2

Text

Parameter can be any value or a column of any type.
Example
Function
Result
if(1 < 2, 'a', 'b')
a

You can also use nested if and else if conditions using the If function. Here are some examples.

Nested If


if(condition1,if(condition2,param1,param2),param3)
If condition1 is true, condition2 is validated. When condition2 is true, returns param1; otherwise returns param2. If condition1 is false, then returns param3.

Example
Function
Result
if(1<3, if(1<2, 'a', 'b'), 'c') a
 

Else if


if(condition1param1, if(condition2,param2,param3))
If condition1 is true, returns param1. When condition1 is false, condition2 is validated. When condition2 is true, returns param2; otherwise returns param3. 

Example
Function
Result
if(3<2, 'a', if(3<4, 'b', 'c')) b





If Null

if_null(param1,param2)

Returns param2 if param1 is null, returns param1 otherwise.

Parameters
Name
Description

param1

Text

Parameter can be any value or a column of any type.

param2

Text

Parameter can be any value or a column of any type.
Example
Function
Result
if_null(null, 'b')
b



Initcap

initcap(param)

Returns the text with the first letter of each word in uppercase. All other letters are in lowercase. Words are delimited by white space.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
initcap('zoho one is an operating system for your business')
Zoho One Is An Operating System For Your Business



Is Null

is_null(param)

Returns true if the expression or column value is null or empty

Parameters
Name
Description

param

Text

Parameter can be a column, or an expression
Example
Function
Result
is_null(null)
true
is_null('A')
false



Is not Null

is_not_null(param)

Returns true if the column value or expression is not null or empty

Parameters
Name
Description

param

Text

Parameter can be a column, or an expression
Example
Function
Result
is_not_null(null)
false
is_not_null('A')
true



Not

not(param)

Applies the NOT logical operation on the column. Returns the opposite value of the Boolean value specified.

Parameters
Name
Description

param

Boolean

Specifies a Boolean value on which the not logical operation is to be applied on.
Example
Function
Result
not(5 > 3)
false



Last Day

last_day(datetime)

Returns the last day of the month mentioned in the given date.

Parameters
Name
Description

datetime

Datetime

Specifies a date column or a datetime column.
Example
Data column
2016-04-09
Function
Result
last_day(Data column)
2016-04-30



Least

least(params)

Returns the least value of all, skipping the null values.

Parameters
Name
Description

params

Decimal

The parameters must be a number column, or a list of numbers or an expression that returns a list of numbers.
Example
Age
[20, 21, 24, 45, 46, 54, 60, 61, 63]
Weight
[123, 145, 160, 155, 200, 50, 134, 55, 60]
Function
Result
least('Age', 'Weight')
20, 21, 24, 45, 46, 50, 60, 55, 60



Left

left(param,length)

Returns the leftmost 'length' of characters from the first parameter, if the length is less than or equal to 0 the result is an empty text.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

length

Number

length must be a number or a column of Number type.
Example
Function
Result
left("NOWHERE", 3)
NOW



Length

length(param)

Returns the character length of the text parameter.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
length("NOWHERE")
7


In

ln(param)

Returns the natural logarithm (base e) of the given parameter.

Parameters
Name
Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
ln('1')
0



Log

log(base,param)

Returns the logarithm of parameter given. This function also accepts custom base value for the logarithm.

Parameters
Name
Description

base

Decimal

Indicates the base of the logarithm function. Parameter must be a number, a numeric column or an expression that returns a number.

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
log(10,100)
2



Log10

log10(param)

Returns the logarithm (base 10) of the parameter given.

Parameters
Name
Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
log10('10.0')
1




Log2

log2(param)

Returns the logarithm (base 2) of parameter given.

Parameters
Name
Description

param

Decimal

Parameter must be a number, a numeric column or an expression that returns a number.
Example
Function
Result
log2('2.0')
1



Lower

lower(param)

The LOWER function returns the given text with lower case for each character.

Parameters
Name
Description

param

Text

Specifies the Text that needs to be converted with lower case for each character. The parameter can be a text value, a text column or an expression that results in a text value.
Example
Function
Result
lower("Zoho, Your Life's Work, Powered By Our Life's Work!")
zoho, your life's work, powered by our life's work!



Itrim

ltrim(param)

Removes the leading white space characters from the text.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
ltrim(" Zoho")
Zoho


Minute

minute(datetime)

Returns the minute component of the timestamp.

Parameters
Name Description

datetime

Datetime

Specifies a Time or a Datetime column.
Example
Data column
2009-07-30 12:58:59
Function Result
minute(Data column) 58



Minute of the day

minute_of_day(datetime)

Returns the minute component of the day from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59
Function
Result
minute_of_day(Data column)
778



Minute of the Hour

minute_of_hour(datetime)

Returns the minute component of an hour from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59
Function
Result
minute_of_hour(Data column)
58



Millisecond of the Day

millisecond_of_day(datetime)

Returns the millisecond of a day from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 00 03 00
Function
Result
millisecond_of_day(Data column)
180000


Millisecond of Second

millisecond_of_second(datetime)

Returns the millisecond of a second from the timestamp.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 12 58 59 850
Function
Result
millisecond_of_second(Data column)
850



Is Leap Year

is_leap_year(date)

Returns true for a leap year and false otherwise.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column .
Example
Data column
2016-04-09
Function
Result
is_leap_year(Date column)
true



Mod

mod(param1,param2)

Returns the remainder after performing numeric division on two numbers.

Parameters
Name
Description

param1

Decimal

Parameter must be a numeric value which is the numerator used in the numeric division.

param2

Decimal

Parameter must be a numeric value which is the denominator used in the numeric division.
Example
Function
Result
mod('7','5')
2



Month

month(date)

Extracts month from the date/timestamp.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
Nov 04
2016
Function
Result
month(Data column)
11


Month as Name

month_name(date)

Returns the month's name of the date/timestamp.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
1997-02-28
Function
Result
month_name(Data column)
February



Month and Year

month_year(date)

Returns the month name of the date/timestamp along with the year.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
1997-02-28
Function
Result
month_year(Data column)
February, 1997



Months in-between

months_between(date1,date2)

Returns the number of months between the two specified dates.

Parameters
Name
Description

date1

Datetime

Specifies a date column or a date-time column from which the difference in months is to be determined.

date2

Datetime

Specifies a date column or a date-time column from which the difference in months is to be determined.
Example
Data column 1
1997-02-28
Data column 2
1997-10-28
Function
Result
months_between('Data column 1', 'Data column 2')
8


Negative

negative(param)

Returns the negated value of the number mentioned in param.

Parameters
Name
Description

param

Decimal

Specifies a number value that is to be negated.
Example
Function
Result
negative('1')
-1



Pi

pi()

Returns the Pi value. The number π is a mathematical constant. It is defined as the ratio of a circle's circumference to its diameter.

Example
Function
Result
pi()
3.1415926536



Power

pow(param1,param2)

Raises a number to the power that is specified by a number.

Parameters
Name
Description

param1

Decimal

Specifies the number which is to be raised to a certain power.

param2

Decimal

Specifies the number which represents the power that the number is to be raised to.
Example
Function
Result
pow('2', '3')
8



Quarter

quarter(date)

Extract quarter of the year, in the range 1 to 4 from the date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2016-08-31
Function
Result
quarter(Data column)
3



Quarter Name

quarter_name(date)

Extract quarter of the year, in the range 1 to 4, with the name of the year from date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2016-08-31
Function
Result
quarter_name(Data column)
Q3



Quarter Year

quarter_year(date)

Extract quarter of the year, in the range 1 to 4, with the year from the date.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
2016-08-31
Function
Result
quarter_year(Data column)
Q3, 2016



Radians

radians(param)

Converts angle in degrees to radian on the column.

Parameters
Name
Description

param

Decimal

angle in degrees
Example
Function
Result
radians(180)
3.1415926536



Random

random(num1,num2)

Generates a random integer value, between the given numbers, in the column.

Parameters
Name
Description

num1

Decimal

Specifies the starting number for the range from which a random number is returned.

num2

Decimal

Specifies the ending number for the range from which a random number is returned.
Example
Function
Result
random('0', '4')
3



Regex Extract

regexp_extract(str,regexp,groupIdx)

Extracts a group that matches regex expression.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regexp

Text

Parameter must be a regular expression pattern passed as a Text.

groupIdx

Number

[Optional] Paramter must be an integer which indicates the index of the group that needs to be extracted.
Example
Function
Result
regexp_extract("100abc", "\\d+" , 0)
100
regexp_extract( "100-abc" , "(\\d+)-([a-zA-Z]+)" , 2)
abc



Regex Replace

regexp_replace(str,regex,replace_with)

Replaces all the subtexts of a text 'str' that matches the regex with the text mentioned in 'replace_with'.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regex

Text

Parameter must be a Text specifying the regular expression.

replace_with

Text

Parameter must be a Text specifying the regular expression.
Example
Function
Result
regexp_replace("100-200", "(\\d+)", 'num')
num-num



Replace

replace(str,search,replace)

Replaces all occurrences of search with the provided replacement text.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

search

Text

A Text expression. If search is not found in str, str is returned unchanged.

replace

Text

A Text expression. If replace is not specified or is an empty text, nothing replaces the text that is removed from str.
Example
Function
Result
replace('ABCabc', 'abc', 'DEF')
ABCDEF



Reverse

reverse(str)

Returns the reversed version of the given text.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
reverse('Zoho One')
enO ohoZ



right(str,len)

Returns the rightmost length `len` of characters from the text `str`. If the length is less than or equal to 0 the result is an empty text.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

len

Number

Specifies the number of characters from the right of the Text to be returned.
Example
Function
Result
right('Zoho One', '3')
One



Round Number

round_num(param,d)

Returns a decimal number rounded to a specified number of decimal places using HALF_EVEN rounding mode.

Parameters
Name
Description

param

Decimal

Specifies the number column or the exact decimal number to be rounded off to the nearest value.

d

Number

Specifies the number column or the exact number of decimal places that the number should be rounded off to.
Example
Function
Result
round_num('2.5', '0')
2
round_num('5.5', '0')
6
round_num('3.2', '0')
3


rtrim

rtrim(str)

Removes the trailing space characters from the provided text value.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
rtrim('Zoho One ')
Zoho One



Second

second(datetime)

Returns the seconds component of the date time. The function also supports unix timestamp in milliseconds as the input.

Parameters
Name Description

datetime

Datetime

Specifies a Time or a Datetime column.
Example
Data column
2009-07-30 12:58:59
Function Result
second(Data column) 59


Second of the Day

second_of_day(datetime)

Returns the second component of a day from the timestamp. The function also supports unix timestamp in milliseconds as the input.

Parameters
Name
Description

datetime

Datetime

Specifies a date-time column.
Example
Data column
2009-07-30 00 11 00
Function
Result
second_of_day(Data column)
660


SHA1

Syntaxsha(param)

Returns a sha1 hash value as a hex text of the param.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
sha('Zoho One')
a74457c58f9d3e564bf534d96e1d85d6e513dac4



Soundex

soundex(str)

Returns the Soundex code of the text values in the column.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
soundex('Miller')
M460


Square Root
sqrt(param)

Returns the square root of the values on the column.

Parameters
Name
Description

param

Decimal

Parameter must be a number or an expression that results in a numeric value or a Number column.
Example
Function
Result
sqrt('64')
8



Substring

substr(sentence,pos,len)

Returns the subtext of the sentence text that starts at a position index and is of a given length.

Parameters
Name
Description

sentence

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

pos

Number

Parameter must be a number representing the index of the starting position of the text to be extracted.

len

Number

[Optional] Parameter must be a number representing the length of the text to be extracted from the 'sentence'
Example
Function
Result
substr('Zoho One', '4', '1')
o



Trim

trim(param)

Removes the leading and trailing space characters from the text values in the column.

Parameters
Name
Description

param

Text

Parameter can be a column of any datatype.
Example
Function
Result
trim(' Zoho One ')
Zoho One



Upper

upper(param)

Returns a text with all of the characters changed to uppercase.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
upper('Zoho One')
ZOHO ONE



Week of the Year

week_of_year(date,startDay,week_mode)

Returns the week of year (in number) from the date or timestamp (a week is considered to start on a Monday and week 1 is the first week with more than 3 days).

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.

startDay

Number

[Optional] Specifies the starting day of the week. The value range from 1 to 7 in this order. [1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday]. The default value is 1.

week_mode

Number

[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1.
Example
Data column
06/12/2018
Function
Result
week_of_year(Data column, 1, 1)
49



Week of the Month

week_of_month(date)

Returns the week of the month of the given date.

Parameters
Name
Description

date

Datetime

Specifies the date or a date-time.
Example
Data column
06/13/2018
Function
Result
week_of_month(Data column)
Week 2


Week of the Year with Year

week_of_year_with_year(datetime,startDay,week_mode)

Returns the week of the year with year from the given date

Parameters
Name
Description

datetime

Datetime

Parameter must be a datetime type column

startDay

Number

[Optional] Specifies the starting day of the week. The value range from 1 to 7 in this order. [1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday]. The default value is 1.

week_mode

Number

[Optional] Week mode determines how the function works. Values allowed are '1' (ISO Week Mode - Minimal 4 days), '2' (week starts from January 1st of the calendar year). The default value is 1.
Example
Data column
06 Dec 2018
17 Nov 2019
Function
Result
week_of_year_with_year(Data column, 1, 1)
Week 49 2018, Week 46 2019



Year

year(date)

Returns the year from the given date

Parameters
Name
Description

date

Datetime

Parameter must be a date or a datetime column
Example
Data column
06 Dec 2018
17 Nov 2019
Function
Result
year(Data column)
2018, 2019


Century

century(date)

Returns the century of the given date or the date-time input.

Parameters
Name
Description

date

Datetime

Parameter must be of date type or a date-time type.
Example
Data column
06 Dec 2018
17 Nov 2019
Function
Result
century(Data column)
21



Extract Date

extract_date(datetime)

Returns the date part from the given date column, or a date-time column.

Parameters
Name
Description

datetime

Datetime

Parameter must be of datetime type or an expression that returns a datetime.
Example
Data column
2009-03-17 20 18 34 543
Function
Result
extract_date(Data column)
2009-03-17



Extract Time

extract_time(datetime)

Returns the time part from the given date-time column.

Parameters
Name
Description

datetime

Datetime

Parameter must be of datetime type or an expression that returns a datetime.
Example
Data column
2009-05-07 12 03 34
Function
Result
extract_time(Data column)
12:03:34



Add Years

add_years(datetime,num_of_years)

Adds the specified number of years to the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_years

Decimal

Specifies the number of years by which to increment the timestamp value.
Example
Data column
03/25/2009
Function
Result
add_years(Data column, '3')
03/25/2012



Sub years

sub_years(datetime,num_of_years)

Subtracts the specified number of years from the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column or the exact date to decrement.

num_of_years

Decimal

Specifies the number of years by which to decrement the timestamp value.
Example
Data column
06/12/2018
Function
Result
sub_years(Data column, '3')
06/12/2015



Difference in Years

diff_in_years(startDate,endDate)

Returns the number of years between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column, the exact start date or an expression that results in a date value.

endDate

Datetime

Specifies a date column or a date-time column, the exact end date or an expression that results in a date value.
Example
Data column 1
2013-08-10
Data column 2
2009-03-07
Function
Result
diff_in_years(Data column 1, Data column 2)
4



Add Weeks

add_weeks(datetime,num_of_weeks)

Adds the specified number of weeks to the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to increment.

num_of_weeks

Decimal

Specifies the number of weeks by which to increment the timestamp value.
Example
Data column
03/25/2009
Function
Result
add_weeks(Data column, '2')
04/08/2009



Sub Weeks

sub_weeks(datetime,num_of_weeks)

Subtracts the specified number of weeks in the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column or the exact date to decrement.

num_of_weeks

Decimal

Specifies the number of weeks by which to decrement the timestamp value.
Example
Data column
06/12/2018
Function
Result
sub_weeks(Data column, '2')
11/22/2018



Difference in Weeks

diff_in_weeks(startDate,endDate)

Returns the number of weeks between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column, the exact start date or an expression that results in a date value.

endDate

Datetime

Specifies a date column or a date-time column, the exact end date or an expression that results in a date value.
Example
Data column 1
2009-08-10
Data column 2
2009-09-17
Function
Result
diff_in_weeks(Data column 1, Data column 2)
7



Add Months

add_months(date,num_of_months)

Adds the specified number of months to the specified date value

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column

num_of_months

Decimal

Specifies the number of months to add to the date value
Example
Data column
2009-11-25
Function
Result
add_months(Data column, `3`)
2010-02-25


Sub Months

sub_months(datetime,num_of_months)

Subtracts the specified number of months from the given date value.

Parameters
Name
Description

datetime

Datetime

Specifies a date column or a date-time column.

num_of_months

Decimal

Specifies the number of months by which to decrement the date.
Example
Data column
19/12/2016
Function
Result
sub_months(Data column, '3')
19/09/2016



Difference in Months

diff_in_months(startDate,endDate)

Returns the number of months between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column.

endDate

Datetime

Specifies a date column or a date-time column.
Example
Data column 1
2009-08-17
Data column 2
2009-03-10
Function
Result
diff_in_months(Data column 1, Data column 2)
5



Add Days

add_days(date,num_of_days)

Adds the specified number of days to the specified date value.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column to increment.

num_of_days

Decimal

Specifies the number of days by which to increment the dates.
Example
Data column
12/25/2009
Function
Result
add_days(Data column, `30`)
01/24/2010



Sub Days

sub_days(date,num_of_days)

Subtracts the specified number of days from the specified date value.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column to decrement.

num_of_days

Decimal

Specifies the number of days by which to decrement the date.
Example
Data column
2009-12-25
Function
Result
sub_days(Data column, '5')
2009-12-20


Difference in Days

diff_in_days(startDate,endDate)

Returns the number of days between startDate and endDate.

Parameters
Name
Description

startDate

Datetime

Specifies a date column or a date-time column.

endDate

Datetime

Specifies a date column or a date-time column.
Example
Data column 1
2009-03-10
Data column 2
2009-03-07
Function
Result
diff_in_days(Data column 1, Data column 2)
3


Add Hours

add_hours(datetime,num_of_hours)

Adds the specified number of hours to the given Time column or a Datetime column.

Parameters
Name Description

datetime

Datetime

Specifies the Datetime column to increment.

num_of_hours

Decimal

Specifies the number of hours by which the value will be incremented.
Example
Data column
12/08/2018 07:58:59
Function Result
add_hours(Data column, '3') 12/08/2018 10:58:59


Sub Hours

sub_hours(datetime,num_of_hours)

Subtracts the specified number of hours from the given date value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_hours

Decimal

Specifies the number of hours by which to decrement the timestamp value.
Example
Data column
1965-07-27 11 58 59
Function
Result
sub_hours(Data column, 3)
1965-07-27 8:58:59



Difference in Hours

diff_in_hours(startDateTimestamp,endDateTimestamp)

Returns the number of hours between startDate timestamp and endDate timestamp.

Parameters
Name
Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.
Example
Data column 1
2009-03-07 23 20 40 600
Data column 2
2009-03-07 20 18 34 543
Function
Result
diff_in_hours(Data column 1, Data column 2)
3


Add Minutes

add_minutes(datetime,num_of_minutes)

Adds the specified number of minutes to the given Time or a Datetime value.

Parameters
Name Description

datetime

Datetime

Specifies the Time or a Datetime column to increment.

num_of_minutes

Decimal

Specifies the number of minutes by which to increment the timestamp value.
Example
Data column
12/08/2018 11:58:59
Function Result
add_minutes(Data column, '2') 12/08/2018 12:00:59


Sub Minutes

sub_minutes(datetime,num_of_minutes)

Subtracts the specified number of minutes from the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_minutes

Decimal

Specifies the number of minutes by which to decrement the timestamp value.
Example
Data column
2018-07-18 00 10 00
Function
Result
sub_minutes(Data column, 2)
2018-07-18 00:08:00



Difference in Minutes

diff_in_minutes(startDateTimestamp,endDateTimestamp)

Returns the number of minutes between startDate timestamp and endDate timestamp.

Parameters
Name
Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.
Example
Data column 1
2009-03-07 20 20 34 600
Data column 2
2009-03-07 20 18 34 543
Function
Result
diff_in_minutes(Data column1, Data column 2)
2



Add Seconds

add_seconds(datetime,num_of_seconds)

Adds the specified number of seconds to the given Time or a Datetime value.

Parameters
Name Description

datetime

Datetime

Specifies the Time or a Datetime column to increment.

num_of_seconds

Decimal

Specifies the number of seconds by which to increment the timestamp value.
Example
Data column
03/25/2009 11:58:49
Function Result
add_seconds(Data column, '10') 03/25/2009 11:58:59

Sub Seconds

sub_seconds(datetime,num_of_seconds)

Subtracts the specified number of seconds in the given date-time value.

Parameters
Name
Description

datetime

Datetime

Specifies the date-time column to decrement.

num_of_seconds

Decimal

Specifies the number of seconds by which to decrement the timestamp value.
Example
Data column
06/12/2018 00 03 00
Function
Result
sub_seconds(Data column, '10')
06/12/2018 00:02:50


Difference in Seconds

diff_in_seconds(startDateTimestamp,endDateTimestamp)

Returns the number of milliseconds between startDate timestamp and endDate timestamp.

Parameters
Name
Description

startDateTimestamp

Datetime

Specifies a date-time column.

endDateTimestamp

Datetime

Specifies a date-time column.
Example
Data column 1
2009-03-07 20 20 36 600
Data column 2
2009-03-07 20 20 34 543
Function
Result
diff_in_seconds(Data column 1, Data column 2)
02


Invert Case

invert_case(param)

Returns the text with all case inverted i.e. upper case is converted to lower case and vice versa.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
invert_case('Zoho One is an Operating System for your Business')
zOHO oNE IS AN oPERATING sYSTEM FOR YOUR bUSINESS

Alternate Case
alternate_case(string)

Returns a text with alternate lower and upper case for each character of the input text starting with lower case for the first character.

Parameters
Name
Description

string

Text

Specifies the Text that needs to be converted with alternate case for each character.
Example
Function
Result
alternate_case("Zoho, Your Life's Work, Powered By Our Life's Work!")
zOhO, yOuR LiFe's wOrK, pOwErEd bY OuR LiFe's wOrK!


Sentence Case

sentence_case(param)

Returns a text of the values in the columns after applying the sentence case to it.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
sentence_case('welcome to the united states of america')
"Welcome to the united states of america"


Camel Case

camel_case(string)

Returns the text after applying the camel case to it.

Parameters
Name
Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
camel_case("Zoho one is an operating system for your business")
ZohoOneIsAnOperatingSystemForYourBusiness


Title Case

title_case(param)

Returns a text of the values in the columns after applying the title case to it. Title case means that the first letter of each word is capitalized, except for certain small words, such as articles and short prepositions

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
title_case('united states of america')
United States of America


Lines to Space

lines_to_space(param)

Returns a text value where the new lines are converted to spaces.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
lines_to_space('Zoho\nOne')
Zoho One


Space to Lines

space_to_lines(param)

Returns a text value where the spaces are converted to new lines.

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
space_to_lines('Zoho One')
Zoho\nOne


Tab to Space

tab_to_space(param)

Returns a text value where the tabs are converted to spaces.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
tab_to_space('Zoho\tOne')
Zoho One


Space to Tab

space_to_tab(param)

Returns a text value where the spaces are converted to tabs.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
space_to_tab('Zoho One')
Zoho\tOne


To Single Space

to_single_space(param)

Returns a text with multiple spaces into a single space.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
to_single_space('Zoho   Office   Suite')
Zoho Office Suite


To Single Tab

to_single_tab(param)

Returns a text with multiple tab spaces into a sinlge tab space.

Parameters
Name
Description

param

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
to_single_tab('Zoho\t\t\tOffice\tSuiteSQL\tOrg')
Zoho\tOffice\tSuiteSQL\tOrg


Remove Empty Lines

remove_empty_lines(param)

Removes all the empty lines in the column

Parameters
Name
Description

param

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.
Example
Function
Result
remove_empty_lines('Zoho\n\n\nOffice\n\nSuite')
Zoho\nOffice\nSuite


Truncate Character

truncate_char(param1,param2)

Truncates a given number of characters from the input text value.

Parameters
Name
Description

param1

Text

Parameter can be a column of any datatype.

param2

Number

Specifies the number of characters to be removed from the Text. The value of the parameter should be smaller than the length of the Text.
Example
Function
Result
truncate_char(Zoho One, 4)
Zoho


Truncate Words

truncate_words(param1,param2)

Truncates a given number of words from the input text value.

Parameters
Name
Description

param1

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

param2

Number

Specifies the number of words to be removed from the Text. The value of the parameter should be smaller than the number of words in the Text and greater than 0.
Example
Function
Result
truncate_words('Zoho One', 1)
Zoho


Remove Space

remove_space(str)

Removes all the spaces in the column.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.
Example
Function
Result
remove_space(' Zoho One ')
"ZohoOne"


To Unix Timestamp

to_unix_timestamp(date)

Returns the UNIX timestamp of the given date time values.

Parameters
Name
Description

date

Datetime

Specifies a date column or a date-time column.
Example
Data column
06/12/2018 00 03 00
Function
Result
to_unix_timestamp(Data column)
1544034780


Is Missing

is_missing(param)

Returns true for missing values, false otherwise.

Parameters
Name
Description

param

Text

Parameter can be a column of any datatype.
Example
Sales
11
12
' '
67
Function
Result
is_missing(Sales)
[false, false, true, false]


Is Valid

is_valid(param1,param2)

Returns true for valid values; false otherwise.

Parameters
Name
Description

param1

Text

Parameter must be a column conforming to any one data type.

param2

Text

Data type to verify if param1 is valid. It can be one of these values: text, date, number, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types
Example
Column1
"ABC"
"DEF"
"GHI"
Function
Result
is_valid(`Column1`,'text')
TRUE


Is Invalid

is_invalid(param1,param2)

Returns true for invalid values; false otherwise.

Parameters
Name
Description

param1

Text

Parameter must be a column conforming to any one data type.

param2

Text

Data type to verify if param1 is invalid. It can be one of these values: text, date, number, datetime, decimal, currency, length, temperature, boolean, email, URL, list, map and custom data types
Example
Column1
"ABC"
"DEF"
"GHI"
Function
Result
is_invalid(`Column1`, 'Number')
TRUE


Size

size(param)

Returns the size of a list. Returns -1 if null.

Parameters
Name
Description

param

List

Parameter must be a column of type List, or a group of values as a list.
Example
Function
Result
size(['b', 'd', 'c', 'a'])
4


List Concat

list_concat(param1,param2)

Returns a list that contains all the elements in the first list followed by all the elements in the second. The two input lists must contain items in the same data structure format.

Parameters
Name
Description

param1

List

Specifies the first of the two lists to concatenate.

param2

List

Specifies the second of the two lists to concatenate.
Example
column1
[1,2,3,4,5]
column2
["A","B,"C","D","E" ]
Function
Result
list_concat(column1, column2)
["1","2","3","4","5","A","B","C","D","E"]


List Unique

list_unique(param)

Filters the list by removing duplicate entries from it. Returns the filtered list.

Parameters
Name
Description

param

List

Parameter must be of type list or a List column.
Example
list
[30, 40, 50, 30, 70, 30, 90]
Function
Result
list_unique(list)
["40", "50", "70", "90"]


List Intersect

list_intersect(param1,param2)

This function compares the values of two lists, and returns only the matching elements. The two lists must contain items in the same data structure format.

Parameters
Name
Description

param1

List

The list to compare from.

param2

List

A list to compare against.
Example
list1
[1,2,3,4,5]
list2
["A", 1, "B" ,2, "C", 3, "D", 4, "E"]
Function
Result
list_intersect(list1, list2)
["1", "2", "3", "4"]


List Zip

list_zip(param1,param2)

Converts two unidimensional list columns into a multi dimensional list.

Parameters
Name
Description

param1

List

Parameter must be of type list or a List column.

param2

List

Parameter must be of type list or a List column.
Example
list1
["A","B","C"]
list2
["D","E","F"]
Function
Result
list_zip(list1 , list2)
[["A","D"],["B","E"],["C","F"]]


Split

split(str,regex)

Splits the text around occurrences that match the regular expression.

Parameters
Name
Description

str

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

regex

Text

Parameter must be a Text mentioning the regular expression that returns a Text.
Example
Function
Result
split('oneAtwoBthreeC', '[ABC]')
["one","two","three",""]


Collect Set

collect_set(param)

Collects the distinct values from a column as a list.

Parameters
Name
Description

param

Text

Parameter must be a column conforming to any one data type.
Example
Column1
"Australia"
"United States"
"Australia"
"Canada"
"Canada"
Function
Result
collect_set(Column1)
["Australia","United States","Canada"]


Contains

contains(string1,string2,num)

Returns true if the text string1 contains string2

Parameters
Name
Description

string1

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

string2

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

num

Number

Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the Text. Using any other number will render the search case-insensitive.
Example
Function
Result
contains("Pineapple", "Apple", 0)
True


Starts With

starts_with(string1,string2,number)

Compares two text columns and returns true if the text string1 starts with string2.

Parameters
Name
Description

string1

Text

Specifies Text values or a Text column.

string2

Text

Specifies Text values or a Text column

number

Number

Specifies if the comparison should be case-sensitive (input '1') or non case-sensitive (input '0').
Example
Function
Result
starts_with('Sunday', 'Sun', '1')
True
starts_with('Sunday', 'Mon', '0')
False


Ends With

ends_with(string1,string2,num)

Returns true if the text string1 ends with string2.

Parameters
Name
Description

string1

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

string2

Text

Parameter must be a Text, a Text column, or an expression that returns a Text.

num

Number

Parameter must be a numeric value. Using '0' will make the search case-sensitive in finding the sting. Using any other number will render the search case-insensitive.
Example
Function
Result
ends_with("Newyork","york", 0)
True


Extract Date With Format

extract_date_with_format(datetime,format)

Returns the date component from date column in the given format.

Parameters
Name
Description

datetime

Date

Parameter must be a date column or a date-time column.

format

Text

Specifies the format in which the date should be extracted by the function.
Example
Data column
2009-05-17
Function
Result
extract_date_with_format(Data column, 'dd-MM-yyyy')
17-05-2009


Extract Time With Format

extract_time_with_format(datetime,format)

Returns the time component from datetime column in the given format.

Parameters
Name
Description

datetime

Datetime

Parameter is mandatory and it must be a date-time type column.

format

Text

Parameter is mandatory and it must be a Text representing the time format to extract.
Example
Data column
2009-05-07 12 03 34 433
Function
Result
extract_time_with_format(Data column, 'hh:mm:ss:SSS')
12:03:34:433


Keyword Extraction

keyword_extraction(string,maxKeywordLength,topElements,model)

Returns the keywords present in the column.

Parameters
Name
Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

maxKeywordLength

Number

Maximum number of words in a single keyword set. For example, for the sentence, 'Operating Systems' key length can be at most two.

topElements

Number

Pick top N elements from results.

model

Text

Three models are supported. They are namely, ONLY_VALID_KEYWORDS, RANKED_KEYWORDS, RANKED_KEYWORDS_WITH_INDEX.
Example
Function
Result
keyword_extraction(`A room without books is like a body without a soul.` , '1' , '4' , 'ONLY_VALID_KEYWORDS')
('room','books','body','soul')


Language Prediction

lang_predict(string,langType)

Detect language from the given text. Supports 71 languages.

Parameters
Name
Description

string

Text

Parameter must be a Text or a Text column, or an expression that returns a Text.

langType

Text

Parameter must be either be 'code', or 'name'. Code will display the language code as a result, whereas name will display the name of the language.
Example
Function
Result
lang_predict('மதிப்புரைகளைப் படிக்கவும்' , 'name')
Tamil
lang_predict('மதிப்புரைகளைப் படிக்கவும' , 'code')
ta


Min If

min_if(num,condition)

Returns the min value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
min_if(ticket_count, ticket_count!=30)
40


Max If

max_if(num,condition)

Returns the max value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
max_if(ticket_count, ticket_count!=90)
80


Avg If

avg_if(num,condition)

Returns the average value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
avg_if(ticket_count, ticket_count!=30)
65


Sum If

sum_if(num,condition)

Returns the sum of the values on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter is mandatory and must be a numeric column or value

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
marks
[90, 85, 95, 65]
grade
[A, B, A, D]
Function
Result
sum_if(marks, `grade`=='A')
[185, 185, 185, 185]


Mode If

mode_if(num,condition)

Returns the mode value on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter must be a Number column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
30
40
30
80
90
Function
Result
mode_if(ticket_count, ticket_count!=30)
40


Standard Deviation If

stddev_if(number,condition)

Returns the population standard deviation of the given column after applying the specified condition.

Parameters
Name
Description

number

Decimal

Parameter must be a numeric column's value or an expression that returns a number.

condition

Boolean

Condition must return a boolean value based on which the function is calculated.
Example
Country
India
USA
Germany
Movie
Avatar
Inception
Avatar
Rating
9.432
1.0
2.0
Function
Result
stddev_if(Rating, `Movie`=='Avatar')
5.2552175978


Variance If

variance_if(num,condition)

Returns the variance of the values on satisfying the given condition

Parameters
Name
Description

num

Decimal

Parameter is mandatory and must be a numeric column or an expression that returns a list of numbers

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
sales_amount
9.432
1
2
country
India
USA
India
Function
Result
variance_if(sales_amount, 'country'=='India');
27.6173124, 27.617312, 27.617312


Count If

count_if(col,condition)

Returns the total number of values on satisfying the given condition

Parameters
Name
Description

col

Text

Parameter must be a column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
70
80
90
Function
Result
count_if(ticket_count, ticket_count!=30)
6


Count Distinct

count_distinct(col)

Returns the total number of distinct values in the column

Parameters
Name
Description

col

Text

Parameter must be a column
Example
ticket_count
30
40
30
60
70
60
90
Function
Result
count_distinct(ticket_count);
5


Count Distinct If

count_distinct_if(col,condition)

Returns the total number of distinct values satisfying the given condition

Parameters
Name
Description

col

Text

Parameter must be a column

condition

Boolean

Condition must return a Boolean value based on which the function is calculated
Example
ticket_count
30
40
50
60
30
80
40
Function
Result
count_distinct_if(ticket_count, ticket_count!=30)
4


Range

range(startNum,stopNum,stepNum)

Computes an array of integers beginning from 'startNum' to 'stopNum' having an interval of 'stepNum' between consecutive integers in the range. If the function generates more than 1000 values for a cell, the output is a null value and the 'stopNum' is excluded from the output. 'StepNum' must always be a positive integer. If 'startNum' is greater than 'stopNum', steps are automatically converted to negative values and the output will be a decrement range.

Parameters
Name
Description

startNum

Number

Parameter must be a Number column or a number. Decimal values are not supported.

stopNum

Number

Parameter must be a Number column or a number. Decimal values are not supported.

stepNum

Number

Specifies the step value for the range. Decimal values are not supported.
Example
Function
Result
range('2','14','2')
[2, 4, 6, 8, 10, 12]
range('14','2','2')
[14, 12, 10, 8, 6, 4]


Locate

locate(string1,string2,position)

Returns the position of the first occurrence of the text 'string1' in 'string2' after the 'position' given

Parameters
Name
Description

string1

Text

Parameter must be a Text, a Text column or an expression that returns a Text

string2

Text

Parameter must be a Text, a Text column or an expression that returns a Text

position

Number

[Optional] Specifies the position after which 'string2' should be located.
Example
Function
Result
locate('bar', 'foobarbar')
4
locate('bar', 'foobarbar' , 5)
5


Lpad

lpad(string,length,string2)

Returns the 'string' text left-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.

Parameters
Name
Description

string

Text

Parameter must be a Text, a Text column or an expression that returns a Text

length

Number

Specifies the total length of the output Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value
Example
Function
Result
lpad('abcd', 3, 'A')
Aab
lpad('abcd', 7, 'A')
AAAabcd


Rpad

rpad(string,length,string2)

Returns the 'string' text right-padded with a 'padString' for the 'length' specified. Padding will be applied only when 'length' value is more than the length of the text, if not the output text is shortened to the 'length' parameter.

Parameters
Name
Description

string

Text

Parameter must be a Text column or an expression that returns a Text

length

Number

Specifies the total length of the output Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value
Example
Function
Result
rpad('abcd', 5, 'A')
abcdA
rpad('abcd', 7, 'A')
abcdAAA


Next as List

nest_as_list(params)

Converts the given parameters to a list

Parameters
Name
Description

params

Text

All parameters must be a Text, or a Text column, or an expression that returns a Text
Example
Function
Result
nest_as_list('Arjun', 'Zip code: 308-242-0670', 'Status: New resident')
["Arjun", "Zip code: 99501", "Status: New resident"]


Nest as Map

nest_as_map(params,...)

Converts the given parameters to a map. The first value will be the key, and the second will be the value in making each key-value pair.

Parameters
Name
Description

params

Multiple

All parameters must be a column
Example
details
['Name', 'Arjun', 'Zip code', '308-242-0670']
Function
Result
nest_as_map(details)
{'Name' : 'Arjun', 'Zip code' : '308-242-0670'}


ELT

elt(num,string,...)

Returns the text value available at the index number specified in the first argument

Parameters
Name
Description

num

Number

Parameter must be a numerical value, a numerical column or an expression that returns a number

string

Multiple

All parameters must be a Text column or an expression that returns a Text
Example
Function
Result
elt('5', 'Zoho', 'is the', 'operating system', 'for your', 'business')
business


Field

field(string1,string2,...)

Searches the value in the first argument with all the rest of the arguments and returns the position of the argument where the first match is found

Parameters
Name
Description

string1

Text

First parameter must be a Text, a Text column or an expression that returns a Text

string2

Multiple

Each parameter from the second must be a Text, a Text column or an expression that returns a Text
Example
Function
Result
field('as','has','as','have')
2




Insert

insert(string1,position,length,string2)

Inserts the text string2 in string1 at the index 'position' for the specified 'length'

Parameters
Name
Description

string1

Text

Parameter must be a Text, a Text column or an expression that returns a Text

position

Number

The position where the 'Text2' parameter must be inserted

length

Number

The length of the Text

string2

Text

Specifies the Text that must be added to, or replaced in the 'Text1' value
Example
Function
Result
insert('abcddb', 3, 2, 'efgh')
abefghdb


Repeat

repeat(string,num)

Repeats the given 'string' text for the specified number of times and returns the constructed text.

Parameters
Name
Description

string

Text

Parameter must be a Text column or a Text, or an expression that returns a Text

num