Formula Column In-built Functions

Supported Functions

Zoho Reports provides you with a variety of inbuilt functions which are predefined mathematical formulas designed to perform specific well-known calculations easily.  This section lists the supported inbuilt functions. 

Formula Column In-built functions

The following table gives a list of inbuilt functions provided by Zoho Reports which can be used to create any formula. The inbuilt formulas are categorized/grouped based on the type of function.

Date Functions

FUNCTION DESCRIPTION EXAMPLE
Absolute Month - absmonth(date_column)This function will return month and year of the given date value in the format (Month, yyyy).absmonth('2011/8/7') = August, 2011
Absolute Quarter - absquarter(date_column)This function will return quarter and year of the given date value in the format (Quarter, yyyy).absquarter('2011/8/7') = Q3, 2011
Absolute Week - absweek(date_column)This function will return week and year of the given date value in the format (Week, yyyy).absweek('2011/8/7') = W32, 2011
Add Date -
adddate(date_column, num_of_days)
This function will add the specified number of days to the given date value.adddate('2011/8/7',10) = 2011/8/17
Add Hour -
addhour(date_column, num_of_hours)
This function will add the specified number of hours to the given date value.addhour('2011/8/7',10) = 2011/8/7, 10:00:00
Add Minute -
addminute(date_column, num_of_minutes)
This function will add the specified number of minutes to the given date value.addminute('2011/8/7',10) = 2011/8/7, 00:10:00
Add Month -
addmonth(date_column, num_of_months)
This function will add the specified number of months to the given date value.addmonth('2011/8/7',10) = 2012/6/7
Add Quarter - addquarter(date_column, num_of_quarters)This function will add the specified number of quarters  to the given date value.addquarter('2011/8/7',10) = 2014/2/7
Add Time -
addtime(data_column, time)
This function will returns the day by adding the specified time to the given date value.addtime
('2002/02/21 18:23:26', '01:20:10') = 2002/02/21 19:43:36
Add Second - addsecond(date_column, num_of_seconds)This function will add the specified number of seconds to the given date value.addsecond('2011/8/7',10) = 2011/8/7, 00:00:10
Add Week -
addweek(date_column, num_of_week)
This function will add the specified number of week to the given date value.addweek('2011/8/7',10) = 2011/10/16
Add Year -
addyear(date_column, num_of_year)
This function will add the specified number of years to the given date value.addyear('2011/8/7',10) = 2021/8/7
Convert String to Date -
convert_string_to_date(string_column, date_formate_to_be_converted)
This function converts the string into date and returns it in the specified format.convert_string_to_date( "Date", '%d/%m/%y')
Convert Time Zone -
converttimezone(date_column, current_timezone, timezone_to_be_converted)
This function returns the date and time value in converted timezone.converttimezone('2004/1/1 12:00:00','+00:00','+10:00' = 2004/1/1 22:00:00

Created Time - 
createdtime()

This function returns the created time of the record.  
Current Date -
currentdate()
This function will return the current date of the computer or server.currentdate() = 15 Sep, 2011 10:06:18
Date -
date(date_column)
This function will return the current date of the computer or server.currentdate() = 15 Sep, 2011 10:06:18
Date and Time Dif -
dateand
timediff(Unit, From Date, To Date[optional]
This function returns the date part of the given date and time value.date('2011/8/7, 05:54:10') = 2011/8/7
Date Diff -
datediff(date_column1, date_column2)
This function will returns the difference between the two given date columns.datediff('2011/8/11','2010/9/11') = 334
Day -
day(date_column)
This function will return the day of the given date value.day( '2011/9/9') = 9
Day of Week - dayofweek(date_column)This function will return the number of the day of week of the given date value (Sunday = 1, Monday = 2,...).dayofweek('2011/9/9') = 6
Day of Year -
dayofyear(date_column)
This function will return the number of the day of the year of the given date value (0 through 365).dayofyear('2011/9/2') = 245
First Date of Current Week -
first_date_current_week
This function returns the day the current week begins with. You can choose to set the start day of your week in the parameter week_start_day by specifying 1 - Sunday, 2- Monday,... 7 - Saturday. By default Sunday is the first day of the week. first_date_current_week( '2011/08/10') = 2011/08/07
From Unixtime - fromunixtime(seconds)This function returns the unix time for the given seconds value.fromunixtime('1000') = 1970/01/01 05:46:40
Hour -
hour(date_column)
This function returns the hour of the given date value.hour('2011/8/7 10:35:23') = 10
Last Day -
lastday(date_column)
This function will return the last day of the month for the given date value.lastday('2011/9/7') = 2011/9/30
Make Date - makedate(year,num_of_days)This function returns the date value for the given year and number of the day value (0 through 365)makedate('2011','23') = 2011/1/23
Minute -
minute(date_column)
This function returns the minutes of the given date value.minute('2011/8/7 10:35:23') = 35
Modified Time -
modifiedtime()   
This function returns the created time of the record (if the record is newly added) or the last modified time of the record. When you apply this function, initially it will return the time at which the formula has been created. Subsequently it will return only the modified time of the record. 
Month Name - monthname(date_column)This function returns the name of the month of the given date value.monthname('2011/9/7') = September
Month Number - monthnum(date_column)This function returns the number of the month of the given date value.monthnum('2011/9/7') = 9
Quarter Name - quartername(date_column)This function returns the quarter name of the given date value.quartername('2011/8/7') = Q3
Quarter Number - quarternum(date_column)This function returns the quarter number of the given date value.quarternum('2011/8/7') = 3
Second -
second(date_column)
This function returns the seconds of the given date/time value.second('2011/9/7 10:35:23') = 23
Sub Date - subdate(date_column,num_of_days)This function returns the date by subtracting the number of days(num_of_days) from the given date value.subdate('2011/9/15','6') = 1990/9/9
Sub Time -
subtime(date_column,time)
This function returns the date by subtracting the time from the given date with time value.subtime('2011/02/21 18:23:26','01:20:10') = 2011/02/21 17:03:16
Table Modified Time -
tabledatamodifiedtime( )
This function returns the last data modified time  of the table. 
Week Day -
weekday(date_column)
This function returns weekday name (Sunday, Monday...) of the given date value.weekday('2011/9/14') = Wednesday
Week of Month -
weekofmonth(date_column) 
This function returns the week number of the month for the given date value.weekofmonth('2011/8/7') = 2
Week of Year -
weekofyear(date_column) 
This function returns the week number of the year for the given date value.weekofyear('2011/8/7') = 31
Year -
year(date_column)
This function returns year from the given date value.year('2011/9/7') = 2011

Numeric Functions

FUNCTIONDESCRIPTIONEXAMPLE
Abs -
abs(numeric_column)
This function returns the absolute value (number without sign) of the 'numeric_column'abs(-10) = 10
Acos -
acos(numeric_column)
This function returns the arc cosine value of the specified 'numeric_column'. Returns NULL if the 'numeric_column' is not in the range-1 to 1.acos(0.5) = 1.047197
Asin -
asin(numeric_column)
This function returns the arc sine value of the specified 'numeric_column'. Returns NULL if the 'numeric_column' is not in the range-1 to 1.asin(0.5) = 0.523598
Atan -
atan(numeric_column)
This function returns the arc tangent value of the specified 'numeric_column'.atan(2) = 1.107149
Atan2 -
atan2(numeric_column1, numeric_column2)
This function returns the arc tangent of the specified columns 'numeric_column1' / 'numeric_column2atan2(0.8, 0.6) = 0.927295
Ceil -
ceil(numeric_column)
This functions rounds the 'numeric_column' to the nearest integer which is greater than the 'numeric_column'ceil(11.56) = 12
Convert Base -
convertbase(N, current_base, to_be_converted_base)
This function returns the converted string representation of the number N, from current base to the to be converted base.convertbase('a', 16, 2) =1010
Cos -
cos(numeric_column)
This function returns the cosine value of the specified 'numeric_column'cos(0) = 1
Cot -
cot(numeric_column)
This function returns the cotangent value of the specified 'numeric_column'cot(0.25) = 3.916317
Degrees -
degrees(numeric_column)
This function returns the angle in Degrees equivalent to the given Radiansdegrees(1) = 57.2957795
Exp -
exp(numeric_column)
This function returns the exponential value of the 'numeric_column'exp(2) = 7.389056
Floor -
floor(numeric_column)
This function rounds the 'numeric_column' to the nearest integer which is less than the 'numeric_column'.floor(11.56) = 11
Greatest - greatest(numeric_column,..., numeric_column)This function gives the greatest of the given arguments.greatest(10,20,5) = 20
Least -
least(numeric_column,..., numeric_column)
This function gives the least of the given arguments.log10(3) = 0.477121
Ln -
ln(numeric_column)
This function returns the natural logarithm of the specified 'numeric_column'ln(5) = 1.609438
Log10 -
log10(numeric_column)
This function returns the logarithm to the base-10 of the specified 'numeric_column'log10(3) = 0.477121
Log2 -
log2(numeric_column)
This function returns the logarithm to the base-2 of the 'numeric_columnlog2(32) = 5
Mod -
mod(numeric_column1, numeric_column2)
This function returns the remainder of the 'numeric_column1' divided by 'numeric_column2'mod(10,3) = 1
Pi -
pi()
This function returns the numeric value of the pi.pi() = 3.14159265358979
Power -
pow(numeric_column1, numeric_column2)
This function returns the value of 'numeric_column1' raised to the power of 'numeric_column2'pow(2,3) = 8
Random -
rand()
This function returns a random value between 0 and 1.rand() = 0.9233482386203
Radians -
radians(numeric_column)
This function returns the angle in radians equivalent to the given degrees.radians(180) = 3.1415926
Round -
round(numeric_column)
This function returns the rounded integer value of the 'numeric_column'.round(10.67) = 11
Sign -
sign(numeric_column)
This function returns -1, 0, or 1, if the 'numeric_column' is negative, zero, or positive.sign(-23) =-1
Sin -
sin(numeric_column)
This function returns the sine value of the 'numeric_column'.sin(0) = 0
Square -
square(numeric_column)
This function returns the square of the specified 'numeric_column'.square(10) = 100
Square Root -
sqrt(numeric_column)
This function returns the square root of the specified 'numeric_column'.sqrt(16) = 4
Tan -
tan(numeric_column)
This function returns the tangent value of the specified 'numeric_column'.tan(0.5) = 0.546302

String Functions

FUNCTIONDESCRIPTIONEXAMPLE
Concat - concat(string_column,...,string_column)This function returns the concatenated string of the given arguments. If any one of the argument is null, it returns null.concat('abcd','ef','db') = abcdefdbd
Concat_WS - concat_ws(separator,string_column1
,....,string_columnN)
This function returns the concatenated string of the given arguments separated by the given separator. If the separator is null, it returns null.concat_ws('-','abcd','ef','db') = abcd-ef-db
Insert -
insert(string_column, start_pos, len, new_string)
This function returns the string 'string_column', with the substring beginning at position 'start_pos' and 'len' characters long replaced by the string 'new_string'. 'start_pos' should be greater than 0. When len is zero, the 'new_string' is inserted previous to the position 'start_pos'.insert('abcddb', 3, 2, 'efgh') = abefghdb
Index of -
indexof(string_column, sub_string)
This function returns the index of the first occurrence of the string 'sub_string' in string 'string_column'.indexof('abcddb','db') = 5
Left -
left(string_column, len)
This function returns the 'len' number of characters from the left-hand side of the string 'string_column'.left('abcdef',3) = abc
Length -
length(string_column)
This function returns the character length of the string.length('abcddb') = 6
Lowercase -
lowercase(string_column)
This function returns the string 'string_column' with all characters changed to lowercase.lowercase('AbCD') = abcd
Locate -
locate(sub_string, string_column, start_pos)c
This function returns the index of the first occurrence of the string 'sub_string' in string 'string_column' starting at the position 'start_pos'.locate('db','zohodbdb',6) = 7
Lpad -
lpad(string_column, len, pad_string)
This function returns the string 'string_column', left-padded to a length of 'len' characters with the string 'pad_string'. If length of the string 'string_column' is greater than 'len', then the first 'len' characters of 'string_column' is returned.lpad('DB',5,'a') = aaaDB
Ltrim -
ltrim(string_column)
This function returns the string 'string_column' with leading spaces removed.ltrim(' abcd') = abcd
Repeat -
repeat(string_column,count)
This function repeats the given 'string_column' for the specified number of times and returns the constructed string.repeat('Abcd',3) = 'AbcdAbcdAbcd'
Replace -
replace(string_column, from_string, to_string)
This function returns the string with all occurrences of the string 'from_str' replaced by the string 'to_str'replace('abcdac','ac','db') = abcddb
Reverse -
reverse(string_column)
This function returns the reverse string of 'string_column'.reverse('abcd') = dcba
Right -
right(string_column, len)
This function returns the 'len' number of characters from the right-hand side of the string 'string_column'right('abcdef',4) = cdef
Rpad -
rpad(string_column, len, pad_string)
This function returns the string 'string_column', right-padded to a length of 'len' characters with the string 'pad_string'. If length of the string 'string_column' is greater than 'len', then the first 'len' characters of 'string_column' is returnedrpad('DB',5,'a') = DBaaa
Rtrim -
rtrim(string_column)
This function returns the string 'string_column' with trailing spaces removed.rtrim('abcd ') = abcd
Soundex -
soundex(string_column)
This function returns a soundex string from 'string_column'. The soundex string is similar for same sounding strings.soundex('hello') = H400
Strcmp -
strcmp(string_column1, string_column2)
This function returns -1 if the 'string_column1' is smaller than the 'string_column2', 0 if the two strings are same, and 1 if the 'string_column1' is greater than the 'string_column2'.strcmp('abcd', 'abcde') =-1
Substring -
substring(string_column, start_pos, string_len)
This function returns -1 if the 'string_column1' is smaller than the 'string_column2', 0 if the two strings are same, and 1 if the 'string_column1' is greater than the 'string_column2'.substring('abcddb', 1, 4) = abcd
substring_index -
substring_index( string_column, delim, delim_count)
This function returns the substring from string str before count occurrences of the delimiter delim.substring_index( 'abcdefgh', 'e', 1) = abc
Trim -
trim(string_column)
This function returns the string with all spaces removed in prefix and suffix of the string.trim(' abcd ') = abcd
Uppercase -
uppercase(string_column)  
This function returns the string 'string_column' with all characters changed to uppercase.uppercase('abcD') = ABCD

Statistical Functions

FUNCTIONDESCRIPTION 
Mean -
mean(numeric_column)
This function returns the mean value of the 'numeric_column' 
Median -
median(numeric_column)
This function returns the middle value in the 'numeric_column' 
Mode -
mode(numeric_column)
This function returns the most common value in the 'numeric_column 

Logical Functions

FUNCTIONDESCRIPTIONEXAMPLE
IF -
if(expr1,expr2,expr3)
This function returns expr2 if expr1 is true else it returns expr3if(5 > 10,100,50) = 50
Ifnull -
ifnull(expr1,expr2)
This function returns expr1 if expr1 is not null, else it return expr2ifnull(null,10) = 10
isnull( ) -
isnull(expr1)
This function returns 1 if expr1 is null, else it returns 0.isnull(null)= 1

General Functions

FUNCTIONDESCRIPTION EXAMPLE
Coalesce - coalesce(null,null,1,...)This function returns 1 if expr1 is null, else it returns 0.coalesce(null,null,4) = 4
Convert Data Type - convertdatatype(column, datatype_to_be_converted)This function returns the given column values after converting them into the specified data type. convertdatatype('123', Number) = 123
To_Integer - to_integer(Column)This function converts the given column to Integer datatype.to_integer('40') = 40
To_Decimal-column - to_decimal(Column), Precision [Optional], Scale)This function converts the given column to Decimal datatype.to_decimal('40') = 40
to_decimal('40',5,3) = 40.000

In-built Aggregate Functions

The following table describes these functions that are more relevant & applicable for creating Aggregate formulas:

FUNCTIONDESCRIPTION
Sum - sum(numeric_column)This function returns  the sum of the 'numeric_column'.
Avg - avg(numeric_column)This function returns  the average of the 'numeric_column'.
Min - Min(numberic_column)This function returns  the minimum of the 'numeric column'.
Max - max(numeric_column)This function returns  the maximum of the 'numeric_column'.
COUNT_WB - count_wb(column)This function returns the number of rows (values) in the given column including null values.
Count - count(column)This function returns the number of rows (values) in the given column.
Stddev - stddev(numeric_column)This function returns the standard deviation of the 'numeric_column'.
Variance - variance(numeric_column)This function returns the variance of the 'numeric_column'.
Distinct Count - count(distinct(column))This function returns the distinct number of values (rows) in the given column.
SumIf - sumif(condition, expr1, expr2)This function returns  the sum of expr1, if condition is true. Else it will return the sum of expr 2. The expr1 and the expr2 can be an expression or just a numeric column. The expr2 is an optional argument. Example: sumif(Region = 'East, Sales, 0) will return the sum of values in the sales column for all the records which match the value East in the Region column. Else it will return 0.
AvgIf - avgif(condition, expr1, expr2)Returns the average of expr1, if condition is true. Else it will return the average of expr 2. The expr1 and the expr2 can be an expression or just a numeric column. The expr2 is an optional argument. Example: avgif(Region = 'East, Sales, 0) will return the average of values in the sales column for all the records which match the value East in the Region column. Else it will return 0.
CountIf- countif(condition)Returns the number of rows (records) that satisfies the condition. Example: countif(Region='East') will return the number of rows (records) in the table which match the value East in the Region column.
YTD -YTD(AggExpr,Date_Col)Year-to-date is a period, starting from the beginning of the current year and ends at the current day.

Arguments:
AggExpr - Should be an Aggregate expression (which uses aggregate functions like SUM, AVG, ... ) for which the Year to date value will be calculated
Date_Col - Should be a date column based on which Year to Date will be calculated.

Example: YTD(Sum(sales), OrderDate) will return the Sum of sales for the current Year till current day, based on date column 'OrderDat

QTD - QTD(AggExpr,Date_Col)Quarter-to-date is a period, starting from the beginning of the current quarter and ends at the current day.

Arguments:
AggExpr - Should be an Aggregate expression (which uses aggregate functions like SUM, AVG, ... ) for which the Quarter to date value will be calculated
Date_Col - Should be a date column based on which Quarter to Date will be calculated.

Example: QTD(Sum(sales), OrderDate) will return the Sum of sales for the current Quarter till current day, based on date column 'OrderDate'.

MTD - MTD(AggExpr,Date_Col)Month-to-date is a period, starting from the beginning of the current month and ends at the current day.

Arguments:
AggExpr - Should be an Aggregate expression (which uses aggregate functions like SUM, AVG, ... ) for which the Month to date value will be calculated
Date_Col - Should be a date column based on which Month to Date will be calculated.

Example: MTD(Sum(sales), OrderDate) will return the Sum of sales for the current Month till current day, based on date column 'OrderDate'.