In-built Formula Column Functions
Analytics Plus offers a wide range of powerful in-built formula column functions to create powerful metrics and address your business needs.
| Function | Description and Example | |
| Date Functions | ||
| Absolute Month - absmonth(date_column) | This function will return month and year from a given date value with the format (Month, yyyy). Example: absmonth('2011/8/7') = August, 2011 | |
| Absolute Quarter - absquarter(date_column) | This function will return Quarter and year from a given date value with the format (Quarter, yyyy). Example: absquarter('2011/8/7') = Q3, 2011 | |
| Add Date - adddate(date_column, num_of_days) | This function will add the specified number of days(num_of_days) to the given date value. Example: adddate('2011/8/7',10) = 2011/8/17 | |
| Add Time - addtime(data_column, time) | Returns the day by adding the time to the given date column. Example: addtime('2002/02/21 18:23:26', '01:20:10') = 2002/02/21 19:43:36 | |
| Current Date - currentdate() | This function will return the current date of the computer or server. Example: currentdate() = 15 Sep, 2011 10:06:18 | |
| Date and Time Dif - dateandtimediff(Unit, From Date, To Date[optional] | This function will return the date and time difference between two date columns based on the units specified. The supported units are SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. Example: dateandtimediff(DAY, '2015-01-01', '2015-05-01')=120 The above example returns the difference between the given dates in Days. | |
| Date Diff - datediff(date_column1, date_column2) | This function will return the difference between the two given date columns. Example: datediff('2011/8/11','2010/9/11') = 334 | |
| Day - day(date_column) | This function will return the day of the given date value. Example: day( '2011/9/9') = 9 | |
| Day of Week - dayofweek(date_column) | This function will return the number of the day of the week of the given date value (Sunday = 1, Monday = 2,...). Example: 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). Example: dayofyear('2011/9/2') = 245 | |
| From Unixtime - fromunixtime(seconds) | This function returns the unix time for the given seconds value. Example: fromunixtime('1000') = 1970/01/01 05:46:40 | |
| Hour - hour(date_column) | This function returns the hour of the given date value. Example: 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. Example: 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). Example: makedate('2011','23') = 2011/1/23 | |
| Minute - minute(date_column) | This function returns the minutes of the given date value. Example: 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 - month(date_column) | This function returns the name of the month of the given date value. Example: month('2011/9/7') = September | |
| Month Num - monthnum(date_column) | This function returns the number of the month of the given date value. Example: monthnum('2011/9/7') = 9 | |
| Quarter - quarter(date_column) | This function returns the quarter of the given date value. Example: quarter('2011/8/7') = Q3 | |
| Second - second(date_column) | This function returns the seconds of the given date/time value. Example: 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. Example: 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. Example: subtime('2011/02/21 18:23:26','01:20:10') = 2011/02/21 17:03:16 | |
| Week Day - weekday(date_column) | This function returns weekday name (Sunday, Monday...) of the given date value. Example: weekday('2011/9/14') = Wednesday | |
| Year - year(date_column) | This function returns year from the given date value. Example: year('2011/9/7') = 2011 | |
| Duration Functions | ||
| Add Duration - add_duration(duration_column, duration_column) | This function returns the duration in the default format ('%D.%H:%m:%s') by adding the values in the specified duration columns. Example: add_duration('3.5:20:30', '1.30:45:40') = 5.12:06:10 | |
| Add Days to Duration - add_days_to_duration(duration_column, num_of_days) | This function returns the duration in the default format ('%D.%H:%m:%s') by adding the number of days to the specified duration column. Example: add_days_to_duration('100.11:22:33', 5)= 105.11:22:33 | |
| Add Hours to Duration - add_hours_to_duration(duration_column, num_of_hours) | This function returns the duration in the default format ('%D.%H:%m:%s') by adding the given number of hours to the specified duration column. Example: add_hours_to_duration('100.11:22:33', 5) = 100.16:22:33 | |
| Add Minutes to Duration - add_minutes_to_duration(duration_column, num_of_minutes) | This function returns the duration in the default format ('%D.%H:%m:%s')by adding the given number of minutes to the specified duration column. Example: add_minutes_to_duration('100.11:22:33', 5) = 100.11:27:33 | |
| Add Seconds to Duration - add_seconds_to_duration(duration_column, num_of_seconds) | This function returns the duration in the default format ('%D.%H:%m:%s') by adding the given number of seconds to the specified duration column. Example: add_seconds_to_duration('100.11:22:33', 5) = 100.11:22:38 | |
| Add Weeks to Duration - add_weeks_to_duration(duration_column, num_of_weeks) | This function returns the duration in the default format ('%D.%H:%m:%s') by adding the given number of weeks to the specified duration column. Example: add_weeks_to_duration('100.11:22:33', 5) = 135.11:22:33 | |
| Duration to Month - Duration_to_months(duration_column) | This function returns the total number of months present in the duration value. Example: duration_to_months('500.10:35:23') = 16 | |
| Duration to Days - duration_to_days(duration_column) | This function returns the total number of days present in the duration argument. Example: duration_to_days('500.10:35:23') = 500 | |
| Duration to Hours - duration_to_hours(duration_column) | This function returns the total number of hours present in the duration argument. Example: duration_to_hours('500.10:35:23') = 12010 | |
| Duration to Minutes - duration_to_minutes(duration_column) | This function returns the total number of minutes present in the duration argument. Example: duration_to_minutes('500.10:35:23') = 720635 | |
| Duration to Seconds - duration_to_seconds(duration_column) | This function returns the total number of seconds present in the duration argument. Example: duration_to_seconds('500.10:35:23') = 43238123 | |
| Duration to Weeks - duration_to_weeks(duration_column) | This function returns the total number of weeks present in the duration argument. Example: duration_to_weeks('500.10:35:23') = 71 | |
| Duration to Years - duration_to_years(duration_column) | This function returns the total number of years present in the duration argument. Example: duration_to_years('500.10:35:23') = 1 | |
| Make Duration - make_duration(num_of_years, num_of_months, num_of_weeks, num_of_days, num_of_hours, num_of_minutes, num_of_seconds) | This function returns the duration in the default format ('%D.%H:%m:%s') by calculating the given number of years, months, weeks, days, hours, minutes and seconds. Enter 0 if any of the specified arguments (years, months, weeks, days, hours, minutes, and seconds) does not have a value. Example: make_duration('1', '11', 0, '21', '9', 50,' 5.777') = 721.10:59:23 | |
| Subtract Duration - sub_duration(duration_column, duration_column) | This function returns the duration in the default format ('%D.%H:%m:%s') by subtracting the values in the specified duration columns. Example: sub_duration('2.01:00:00', '5.05:03:04') = -3.04:03:04 | |
| Subtract Days from Duration - sub_days_from_duration(duration_column, num_of_days) | This function returns the duration in the default format ('%D.%H:%m:%s') by subtracting the given number of days to the specified duration column. Example: sub_days_from_duration('9.07:06:06', 10) = -0.16:53:54 | |
| Subtract Hours from Duration - sub_hours_from_duration(duration_column, num_of_hours) | This function returns the duration in the default format ('%D.%H:%m:%s') by subtracting the given number of hours to the specified duration column. Example: sub_hours_from_duration('9.02:16:45', 45) = 7.05:16:45 | |
| Subtract Minutes from Duration - sub_minutes_from_duration(duration_column, num_of_minutes) | This function returns the duration in the default format ('%D.%H:%m:%s') by subtracting the given number of minutes to the specified duration column. Example: sub_minutes_from_duration('8.04:08:06', 75) = 8.02:53:06 | |
| Subtract Seconds from Duration - sub_seconds_from_duration(duration_column, num_of_seconds) | This function returns the duration in the default format ('%D.%H:%m:%s') by subtracting the given number of seconds to the specified duration column. Example: sub_seconds_from_duration('9.07:06:06', 15) = 9.07:05:51 | |
| Subtract Weeks from Duration - sub_weeks_from_duration(duration_column, num_of_weeks) | This function returns the duration in the default format ('%D.%H:%m:%s') by subtracting the given number of weeks to the specified duration column. Example: Sub_weeks_from_durrom_duration('200.07:06:06', 5) = 165.07:06:06 | |
| To Duration - to_duration(numeric_column) | Converts the given column with number of seconds into Duration. Example: to_duration(23540) = 0.06:32:20 | |
| String Functions | ||
| Concat - concat(string_column,...,string_column) | Returns the concatenated string of the given arguments. If any one of the argument is null, it returns null. Example: concat('abcd','ef','db') = abcdefdbd | |
| Concat_WS - concat_ws(separator,string_column1,....,string_columnN) | Returns the concatenated string of the given arguments separated by the given separator. If the separator is null, it returns null. Example: concat_ws('-','abcd','ef','db') = abcd-ef-db | |
| Insert - insert(string_column, start_pos, len, new_string) | 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'. Example: insert('abcddb', 3, 2, 'efgh') = abefghdb | |
| Index of - indexof(string_column, sub_string) | Returns the index of the first occurrence of the string 'sub_string' in string 'string_column'. Example: indexof('abcddb','db') = 5 | |
| Left - left(string_column, len) | Returns the 'len' number of characters from the left-hand side of the string 'string_column'. Example: left('abcdef',3) = abc | |
| Length - length(string_column) | Returns the character length of the string. Example: length('abcddb') = 6 | |
| Lowercase - lowercase(string_column) | Returns the string 'string_column' with all characters changed to lowercase. Example: lowercase('AbCD') = abcd | |
| Locate - locate(sub_string, string_column, start_pos) | Returns the index of the first occurrence of the string 'sub_string' in string 'string_column' starting at the position 'start_pos'. Example: locate('db','zohodbdb',6) = 7 | |
| Lpad - lpad(string_column, len, pad_string) | 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. Example: lpad('DB',5,'a') = aaaDB | |
| Ltrim - ltrim(string_column) | Returns the string 'string_column' with leading spaces removed. Example: ltrim(' abcd') = abcd | |
| Repeat - repeat(string_column,count) | repeat('Abcd',3) = 'AbcdAbcdAbcd' | |
| Replace - replace(string_column, from_string, to_string) | Returns the string with all occurrences of the string 'from_str' replaced by the string 'to_str'. Example: replace('abcdac','ac','db') = abcddb | |
| Reverse - reverse(string_column) | Returns the reverse string of 'string_column'. Example: reverse('abcd') = dcba | |
| Right - right(string_column, len) | Returns the 'len' number of characters from the right-hand side of the string 'string_column'. Example: right('abcdef',4) = cdef | |
| Rpad - rpad(string_column, len, pad_string) | 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 returned. Example: rpad('DB',5,'a') = DBaaa | |
| Rtrim - rtrim(string_column) | Returns the string 'string_column' with trailing spaces removed. Example: rtrim('abcd ') = abcd | |
| Strcmp - strcmp(string_column1, string_column2) | 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'. Example: strcmp('abcd', 'abcde') =-1 | |
| Substring - substring(string_column, start_pos, string_len) | The substring() method extracts the characters from a string, between two specified indices, and returns the new sub string. Example: substring('abcddb', 1, 4) = abcd | |
| Trim - trim(string_column) | Returns the string with all spaces removed in prefix and suffix of the string. Example: trim(' abcd ') = abcd | |
| Uppercase - uppercase(string_column) | Returns the string 'string_column' with all characters changed to uppercase. Example: uppercase('abcD') = ABCD | |
| Numeric Functions | ||
| Abs - abs(numeric_column) | This function returns the absolute value (number without sign) of the 'numeric_column' Example: pi() = 3.14159265358979 | |
| 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. Example: pow(2,3) = 8 | |
| 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. Example: rand() = 0.282164005825449 | |
| Atan - atan(numeric_column) | This function returns the arc tangent value of the specified 'numeric_column'. Example: atan(2) = 1.107149 | |
| Atan2 - atan2(numeric_column1, numeric_column2) | This function returns the arc tangent of the specified columns 'numeric_column1' / 'numeric_column2. Example: atan2(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'. Example: ceil(11.56) = 12 | |
| Cos - cos(numeric_column) | This function returns the cosine value of the specified 'numeric_column'. Example: cos(0) = 1 | |
| Cot - cot(numeric_column) | This function returns the cotangent value of the specified 'numeric_column'. Example: cot(0.25) = 3.916317 | |
| Degrees - degrees(numeric_column) | This function returns the angle in Degrees equivalent to the given Radians. Example: degrees(1) = 57.2957795 | |
| Exp - exp(numeric_column) | This function returns the exponential value of the 'numeric_column'. Example: exp(2) = 7.389056 | |
| Floor - floor(numeric_column) | Rounds the 'numeric_column' to the nearest integer which is less than the 'numeric_column'. Example: floor(11.56) = 11 | |
| Greatest - greatest(numeric_column,..., numeric_column) | Gives the greatest of the given arguments. Example: greatest(10,20,5) = 20 | |
| Least - least(numeric_column,..., numeric_column) | Gives the least of the given arguments. Example: least(10,20,5) = 5 | |
| Ln - ln(numeric_column) | This function returns the natural logarithm of the specified 'numeric_column'. Example: ln(5) = 1.609438 | |
| Log10 - log10(numeric_column) | This function returns the logarithm to the base-10 of the specified 'numeric_column'. Example: log10(3) = 0.477121 | |
| Log2 - log2(numeric_column) | This function returns the logarithm to the base-2 of the 'numeric_column. Example: log2(32) = 5 | |
| Mod - mod(numeric_column1, numeric_column2) | Returns the remainder of the 'numeric_column1' divided by 'numeric_column2'. Example: mod(10,3) = 1 | |
| Pi - pi() | This function returns the numeric value of the pi. Example: pi() = 3.14159265358979 | |
| Power - pow(numeric_column1, numeric_column2) | This function returns the value of 'numeric_column1' raised to the power of 'numeric_column2'. Example: pow(2,3) = 8 | |
| Random - rand() | Returns a random value between 0 and 1. Example: rand() = 0.9233482386203 | |
| Radians - radians(numeric_column) | Returns the angle in radians equivalent to the given degrees. Example: radians(180) = 3.1415926 | |
| Round - round(numeric_column) | Returns the rounded integer value of the 'numeric_column'. Example: round(10.67) = 11 | |
| Sign - sign(numeric_column) | Returns-1, 0, or 1, if the 'numeric_column' is negative, zero, or positive. Example: sign(-23) =-1 | |
| Sin - sin(numeric_column) | Returns the sine value of the 'numeric_column'. Example: sin(0) = 0 | |
| Square - square(numeric_column) | Returns the square of the specified 'numeric_column'. Example: square(10) = 100 | |
| Square Root - sqrt(numeric_column) | Returns the square root of the specified 'numeric_column'. Example: sqrt(16) = 4 | |
| Tan - tan(numeric_column) | Returns the tangent value of the specified 'numeric_column'. Example: tan(0.5) = 0.546302 | |
| Statistical Functions | ||
| Mean - mean(numeric_column) | Returns the mean value of the 'numeric_column' | |
| Median - (numeric_column) | Returns the middle value in the 'numeric_column' | |
| Mode - mode(numeric_column) | Returns the most common value in the 'numeric_column | |
| Logical Functions | ||
| IF - if(expr1,expr2,expr3) | Returns expr2 if expr1 is true else it returns expr3. Example: if(5> 10,100,50) = 50 | |
| Ifnull - ifnull(expr1,expr2) | Returns expr1 if expr1 is not null, else it return expr2. Example: ifnull(null,10) = 10 | |
| isnull( ) - isnull(expr1) | Returns 1 if expr1 is null, else it returns 0. Example: isnull(null)- 1 | |
| General Functions | ||
| Coalesce - coalesce(null,null,1,...) | Returns 1 if expr1 is null, else it returns 0. Example: coalesce(null,null,4) = 4 | |