Calculated Fields

Introduction

A calculated field is calculated from other fields in the list. The calculated field itself does not exist in the data source. Its value for each row is calculated from the values of the other fields.

The calculated value does not have to be numeric. In fact you can calculate a date, a time duration, or perform some text manipulation to create the value.

Adding a Calculated Field

To see how to add a calculated field, let's see an example. We'll start with this view showing the 10 hottest days in NYC. The temperatures are shown in Celsius.

To also show the temperatures in Fahrenheit, we'll add a calculated field by clicking the calculator button (see arrow in the above picture).

Using the Calculated Field dialog, we can build a calculation that can include arithmetic as well as a selection of functions. For this example, we only need arithmetic.

The following picture shows the list view with the calculated field:

Adding a Function to the Calculation

To demonstrate the use of functions in a calculation, let's continue with our example, and calculate the number of years ago when we saw that temperature.

The calculation extract the year part of the date and subtract it from the year part of today's date. The TODAY() function returns today's date. The YEAR() function returns the year part of the date as a number.

Next is the list view with the "Years Ago" field.

Functions

The table below shows all the functions that are available for field calculations.

• ABS(value) - Absolute value. The value must be numeric and the returned value is based on the type of the value.
• COMPARE(item1, operator, item2, trueValue, falseValue) - compares numeric item1 to item2. The operator is one of = != > >= < <= . If the comparison is true, the result is the value 'true', or if a trueValue is specified, the result is trueValue. If the comparison fails, the result is the value 'false', or if falseValue is specified the result is falseValue
• CONCAT(text,text,...) - concatenates two or more text values returning the concatenated text
• CSV_SEARCH(csv,item1,item2,item3) - Search a comma separated text for an item matching item1. If found, return item 2, otherwise return item3
• CSV_SELECT(csv,n) - Select the nth item in a comma-separated text. Items are numbered from 1 (first)
• DATE_ADD(date,duration) - adds the duration (number of seconds) to the date and returns the new date
• DATE_ADD(date,interval,count) - adds count intervals to the date. Count is the number of intervals and the interval can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND
• DATE_SUB(date,duration) - subtract the duration (number of seconds) from the date and returns the new date
• DATE_SUB(date,interval,count) - subtracts count intervals from the date. Count is the number of intervals and the interval can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND
• DAY(date) - the day of the month - a number between 1 and 31
• DELIMITER_SEARCH(delimiter,text,item1,item2,item3) - Search a delimited text for an item matching item1. If found, return item 2, otherwise return item3
• DELIMITER_SELECT(delimiter,text,n) - Select the nth item in a delimited text. Items are numbered from 1 (first)
• DURATION(begin,end) - Difference in seconds between the begin and end. The returned value is of type duration.
• FORMAT(value,format) - if value is date/time, the format will be a date/time format. The format is specified according to the Java format. For example, 'MM/dd/yyyy HH:mm' would render: 12/06/2014 17:46. If value is numeric, the format will be a number format. When entering a number format, you can enter formats based on the Java Decimal Format specifications.
• HOUR(date) - the hour of the day - a number between 0 and 23
• IFNULL(value,replacement) - If the input is null or empty, replace it with the replacement value. The type of the replacement must agree with the type of the text
• INSTR(text,item,start) - the position of item in text. If start is specified, the search start at the specified start position. The position of the first character is 1. If not found, the position is 0
• LEFT(text,count) - extracts the first count (leftmost) characters of the text
• LOOKUP(value,data source,table,key field,value field) - lookup value in a lookup table. The table is specified by data source and table. The lookup value is matched against the key field. If there's a match, it returns the value of the value field of the lookup table. Lookup is case insensitive. If there's no match, the returned value is empty (null). When specifying the data source, table, and fields, you can use their name or their label.

Please note that when entering the data source, table, key field, and value field, you must enclose those names in single quotes and not double quotes. For example, to lookup a sales_target table in the explore data source, the LOOKUP function might look like this:

LOOKUP("Department", 'explore', 'sales_target', 'department_id', 'target')

• MID(text,start,count) - extracts count characters from the text starting at the start position. The position of the first character is 1. If count is not specified, MID extracts all the characters until the end of the text
• MINUTE(date) - the minute of the hour - a number between 0 and 59
• MONTH(date) - the month of the year - a number between 1 and 12
• NOW() - The current date and time (at the data source of this list view).
• QUARTER(date) - the quarter of the year - a number between 1 and 4
• REPLACE(text,item,replacement) - Replaces all occurrences of text item with replacement. The result is text
• RIGHT(text,count) - extracts the last count (rightmost) characters of the text
• ROUND(value) - rounds a numeric value to an integer
• SEARCH(text,item1,item2,item3) - Search the text for item1. If found, return item 2, otherwise return item3
• SECOND(date) - the second - a number between 0 and 59
• TEXT_COMPARE(item1, operator, item2, trueValue, falseValue) - compares text item1 to item2. The operator is one of = != > >= < <= . The comparison is alphanumeric. If the comparison is true, the result is the value 'true', or if a trueValue is specified, the result is trueValue. If the comparison fails, the result is the value 'false', or if falseValue is specified the result is falseValue
• TO_DATE(text, format) - converts a formatted date to a standard Explore Analytics date (and time). If format is not specified, the text can be in the standard Explore Analytics format (e.g. '2015-10-31 14:01:03') or in mm/dd/yyyy format (e.g., '10/31/2015 14:01:03'). The date may omit the time part. If format is specified, it follows the Java simple date format specifications. Format can also be 'epoch' and it interprets the text to be a number of seconds since 01/01/1970 (known as Unix timestamp) and will convert it to a date in UTC.
• TO_NUMBER(text) - validate that text is a number and return it as a numeric value that can be used in arithmetic. If text is not a valid number, an error message will be issued and the view will not be produced
• TODAY() - The current date. Same as NOW, but with the time part truncated.
• TRUNC(value) - if value is numeric, the fraction part is removed. If value is datetime, the time-of-day part is removed
• TRUNC(date,interval) - truncate the date down to the specified interval. For example, if interval is YEAR, the result is the top of the year (January 1 at 00:00:00). Similarly, QUARTER is top of the quarter, MONTH is top of month, WEEK_MONDAY is the start time of the week (Monday), WEEK_SUNDAY is the beginning of the week (Sunday), DAY is the date without the time part, HOUR is the date and time truncated to the beginning of the hour
• YEAR(date) - the year part of a datetime value returned as a number (e.g. 2015)

⇦ Reporting on ServiceNow Variables

⇧ Table of Contents

⇨ List Mashup