Using calculated fields
There are many cases where you need to adjust or transform the field data that is loaded. For example, you may need to concatenate a first name and a last name to a full name, extract part of a product number, convert the data format or multiply two numbers.
You can add calculated fields to manage many cases like this. A calculated field uses an expression to define the result of the field. You can use functions, fields and operators in the expression. You can only refer to fields in the table that you are editing. You can reference another calculated field in your calculated field.
You add and edit calculated fields in the table editor of the data manager.
To learn more about editing tables, see Editing a table.
Adding a calculated field
Do the following:

Click Add field and select Calculated field.
The editor for Add calculated field opens.
 Type the name of the calculated field in Name.

Define the expression of the calculated field in Expression. There are two different ways to do this.

Use the (Functions), (Fields) and (Operators) lists to select and insert items into the expression.
The item you select is inserted at the cursor position in Expression.

Type the expression for the calculated field in Expression.
As you type, you get assistance with suggested functions and fields, as well as function syntax.
Example results of the calculated field is displayed in Preview.


Click Create to create the calculated field and close the calculated field editor.
Editing a calculated field
You can change the name or edit the expression of a calculated field.
Do the following:

Select Edit from the dropdown menu next to the field name.
The editor for Update calculated field opens.
 Edit the name of the calculated field in Name if you want to change it.

Edit the expression of the calculated field.

Click Update to update the calculated field and close the calculated field editor.
Which functions can I use in a calculated field expression?
You can use the functions listed here when you create a calculated field expression. This is a subset of the expressions available in the data load script. The expression cannot result in any aggregation of data from several records, or use interrecord functions to refer to data in other records.
For more information, see Script expressions.
String functions that can be used in a calculated field expression
These functions can be used to modify or extract data in text string format.
Function  Description 

Capitalize  Capitalize() returns the string with all words in initial uppercase letters. 
Chr  Chr() returns the Unicode character corresponding to the input integer. 
FindOneOf 
FindOneOf() searches a string to find the position of the occurrence of any character from a set of provided characters. The position of the first occurrence of any character from the search set is returned unless a third argument (with a value greater than 1) is supplied. If no match is found, 0 is returned. 
Index  Index() searches a string to find the starting position of the nth occurrence of a provided substring. An optional third argument provides the value of n, which is 1 if omitted. A negative value searches from the end of the string. The positions in the string are numbered from 1 and up. 
KeepChar 
KeepChar() returns a string consisting of the first string ,'text', less any of the characters NOT contained in the second string, "keep_chars". 
Left  Left() returns a string consisting of the first (leftmost) characters of the input string, where the number of characters is determined by the second argument. 
Len  Len() returns the length of the input string. 
Lower  Lower() converts all the characters in the input string to lower case. 
LTrim  LTrim() returns the input string trimmed of any leading spaces. 
Mid  Mid() returns the part of the input string starting at the position of the character defined by the second argument, 'start', and returning the number of characters defined by the third argument, 'count'. If 'count' is omitted, the rest of the input string is returned. The first character in the input string is numbered 1. 
Ord  Ord() returns the Unicode code point number of the first character of the input string. 
PurgeChar  PurgeChar() returns a string consisting of the characters contained in the input string ('text'), excluding any that appear in the second argument ('remove_chars'). 
Repeat  Repeat() forms a string consisting of the input string repeated the number of times defined by the second argument. 
Replace  Replace() returns a string after replacing all occurrences of a given substring within the input string with another substring. The function is nonrecursive and works from left to right. 
Right 
Right() returns a string consisting of the last (rightmost) characters of the input string, where the number of characters is determined by the second argument. 
RTrim  RTrim() returns the input string trimmed of any trailing spaces. 
SubStringCount  SubStringCount() returns the number of occurrences of the specified substring in the input string text. If there is no match, 0 is returned. 
TextBetween 
TextBetween() returns the text in the input string that occurs between the characters specified as delimiters. 
Trim  Trim() returns the input string trimmed of any leading and trailing spaces. 
Upper  Upper() converts all the characters in the input string to upper case for all text characters in the expression. Numbers and symbols are ignored. 
Date functions that can be used in a calculated field expression
Qlik Sense date and time functions are used to transform and convert date and time values.
Functions are based on a datetime serial number that equals the number of days since December 30, 1899. The integer value represents the day and the fractional value represents the time of the day.
Qlik Sense uses the numerical value of the argument, so a number is valid as a argument also when it is not formatted as a date or a time. If the argument does not correspond to a numerical value, for example, because it is a string, then Qlik Sense attempts to interpret the string according to the date and time environment variables.
If the date format used in the argument does not correspond to the one set in the DateFormat system variable, Qlik Sense will not be able to interpret the date correctly. To resolve this, either change the settings or use an interpretation function.
For more information, see DateFormat.
Function  Description 

addmonths  This function returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate. 
addyears  This function returns the date occurring n years after startdate or, if n is negative, the date occurring n years before startdate. 
age  The age function returns the age at the time of timestamp (in completed years) of somebody born on date_of_birth. 
converttolocaltime  Converts a UTC or GMT timestamp to local time as a dual value. The place can be any of a number of cities, places and time zones around the world. 
day 
This function returns an integer representing the day when the fraction of the expression is interpreted as a date according to the standard number interpretation. 
dayend  This function returns a value corresponding to a timestamp of the final millisecond of the day contained in time. The default output format will be the TimestampFormat set in the script. 
daylightsaving  Converts a UTC or GMT timestamp to local time as a dual value. The place can be any of a number of cities, places and time zones around the world. 
dayname  This function returns a value showing the date with an underlying numeric value corresponding to a timestamp of the first millisecond of the day containing time. 
daynumberofquarter  Converts a UTC or GMT timestamp to local time as a dual value. The place can be any of a number of cities, places and time zones around the world. 
daynumberofyear  This function calculates the day number of the year in which a timestamp falls. The calculation is made from the first millisecond of the first day of the year, but the first month can be offset. 
daystart 
This function returns a value corresponding to a timestamp with the first millisecond of the day contained in the time argument. The default output format will be the TimestampFormat set in the script. 
firstworkdate  The firstworkdate function returns the latest starting date to achieve no_of_workdays (MondayFriday) ending no later than end_date taking into account any optionally listed holidays. end_date and holiday should be valid dates or timestamps. 
GMT  This function returns the current Greenwich Mean Time, as derived from the regional settings. 
hour  This function returns an integer representing the hour when the fraction of the expression is interpreted as a time according to the standard number interpretation. 
inday  This function returns True if timestamp lies inside the day containing base_timestamp. 
indaytotime  This function returns True if timestamp lies inside the part of day containing base_timestamp up until and including the exact millisecond of base_timestamp. 
inlunarweek  This function determines if timestamp lies inside the lunar week containing base_date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week., Apart from the final week of the year, each week will contain exactly seven days. 
inlunarweektodate  This function finds if timestamp lies inside the part of the lunar week up to and including the last millisecond of base_date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days. 
inmonth  This function returns True if timestamp lies inside the month containing base_date. 
inmonths  This function finds if a timestamp falls within the same month, bimonth, quarter, fourmonth period, or halfyear as a base date. It is also possible to find if the timestamp falls within a previous or following time period. 
inmonthstodate  This function finds if a timestamp falls within the part a period of the month, bimonth, quarter, fourmonth period, or halfyear up to and including the last millisecond of base_date. It is also possible to find if the timestamp falls within a previous or following time period. 
inmonthtodate  Returns True if date lies inside the part of month containing basedate up until and including the last millisecond of basedate. 
inquarter  This function returns True if timestamp lies inside the quarter containing base_date. 
inquartertodate  This function returns True if timestamp lies inside the part of the quarter containing base_date up until and including the last millisecond of base_date. 
inweek  This function returns True if timestamp lies inside the week containing base_date. 
inweektodate  This function returns True if timestamp lies inside the part of week containing base_date up until and including the last millisecond of base_date. 
inyear  This function returns True if timestamp lies inside the year containing base_date. 
inyeartodate  This function returns True if timestamp lies inside the part of year containing base_date up until and including the last millisecond of base_date. 
lastworkdate  The lastworkdate function returns the earliest ending date to achieve no_of_workdays (MondayFriday) if starting at start_date taking into account any optionally listed holiday. start_date and holiday should be valid dates or timestamps. 
localtime  This function returns a timestamp of the current time for a specified time zone. 
lunarweekend  This function returns a value corresponding to a timestamp of the last millisecond of the last day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days. 
lunarweekname  This function returns a display value showing the year and lunar week number corresponding to a timestamp of the first millisecond of the first day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1as the first day of the week and, apart from the final week of the year, will contain exactly seven days. 
lunarweekstart  This function returns a value corresponding to a timestamp of the first millisecond of the first day of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting January 1 as the first day of the week and, apart from the final week of the year, will contain exactly seven days. 
makedate  This function returns a date calculated from the year YYYY, the month MM and the day DD. 
maketime  This function returns a time calculated from the hour hh, the minute mm, and the second ss. 
makeweekdate  This function returns a date calculated from the year, the week number, and the day of week . This function returns a date calculated from the year YYYY, the week WW and the day of week D. 
minute  This function returns an integer representing the minute when the fraction of the expression is interpreted as a time according to the standard number interpretation. 
month  This function returns a dual value: a month name as defined in the environment variable MonthNames and an integer between 112. The month is calculated from the date interpretation of the expression, according to the standard number interpretation. For more information, see MonthNames. 
monthend  This function returns a value corresponding to a timestamp of the last millisecond of the last day of the month containing date. The default output format will be the DateFormat set in the script. 
monthname  This function returns
a display value showing the month (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding
to a timestamp of the first millisecond of the first day of the month.
For more information, see MonthNames. 
monthsend  This function returns a value corresponding to a timestamp of the last millisecond of the month, bimonth, quarter, fourmonth period, or halfyear containing a base date. It is also possible to find the timestamp for a previous or following time period. 
monthsname  This function returns a display value representing the range of the months of the period (formatted according to the MonthNames script variable) as well as the year. The underlying numeric value corresponds to a timestamp of the first millisecond of the month, bimonth, quarter, fourmonth period, or halfyear containing a base date. 
monthsstart  This function returns a value corresponding to the timestamp of the first millisecond of the month, bimonth, quarter, fourmonth period, or halfyear containing a base date. It is also possible to find the timestamp for a previous or following time period.The default output format is the DateFormat set in the script. 
monthstart  This function returns a value corresponding to a timestamp of the first millisecond of the first day of the month containing date. The default output format will be the DateFormat set in the script. 
networkdays  The networkdays function returns the number of working days (MondayFriday) between and including start_date and end_date taking into account any optionally listed holiday. 
now  This function returns a timestamp of the current time. The function returns values in the TimeStamp system variable format. The default timer_mode value is 1. 
quarterend  This function returns a value corresponding to a timestamp of the last millisecond of the quarter containing date. The default output format will be the DateFormat set in the script. 
quartername  This function returns a display value showing the months of the quarter (formatted according to the MonthNames script variable) and year with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the quarter. 
quarterstart  This function returns a value corresponding to a timestamp of the first millisecond of the quarter containing date. The default output format will be the DateFormat set in the script. 
second  This function returns an integer representing the second when the fraction of the expression is interpreted as a time according to the standard number interpretation. 
timezone  This function returns the time zone, as defined on the computer where the Qlik engine is running. 
today  This function returns the current date. The function returns values in the DateFormat system variable format. 
UTC  Returns the current Coordinated Universal Time. 
week  This function returns an integer representing the week number according to ISO 8601. The week number is calculated from the date interpretation of the expression, according to the standard number interpretation. 
weekday  This function returns a dual value with: A day name as defined in the environment variable DayNames. An integer between 06 corresponding to the nominal day of the week (06). 
weekend  This function returns a value corresponding to a timestamp of the last millisecond of the last day of the calendar week containing date. The default output format will be the DateFormat set in the script. This function returns a value corresponding to a timestamp of the last millisecond of the last day (Sunday) of the calendar week containing date. The default output format will be the DateFormat set in the script. 
weekname  This function returns a value showing the year and week number with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the week containing date. 
weekstart  This function returns a value corresponding to a timestamp of the first millisecond of the first day of the calendar week containing date. The default output format is the DateFormat set in the script. 
weekyear  This function returns the year to which the week number belongs according to the environment variables. The week number ranges between 1 and approximately 52.This function returns the year to which the week number belongs according to ISO 8601. The week number ranges between 1 and approximately 52. 
year  This function returns an integer representing the year when the expression is interpreted as a date according to the standard number interpretation. 
yearend  This function returns a value corresponding to a timestamp of the last millisecond of the last day of the year containing date. The default output format will be the DateFormat set in the script. 
yearname  This function returns a fourdigit year as display value with an underlying numeric value corresponding to a timestamp of the first millisecond of the first day of the year containing date. 
yearstart  This function returns a timestamp corresponding to the start of the first day of the year containing date. The default output format will be the DateFormat set in the script. 
yeartodate  This function finds if the input timestamp falls within the year of the date the script was last loaded, and returns True if it does, False if it does not. 
Formatting and interpretation functions that can be used in a calculated field expression
The formatting functions use the numeric value of the input expression, and convert this to a text value. In contrast, the interpretation functions do the opposite: they take string expressions and evaluate them as numbers, specifying the format of the resulting number. In both cases the output value is dual, with a text value and a numeric value.
For example, consider the differences in output between the Date and the Date# functions.
Function  Output (text)  Output (numeric) 

Date#('20140831', 'YYYYMMDD')  20140831  41882 
Date(41882, 'YYYY.MM.DD')  2014.08.31  41882 
These functions are useful when your data contains date fields that are not interpreted as dates as the format does not correspond to the date format setting in Qlik Sense. In this case, it can be useful to nest the functions:
Date(Date#(DateInput, 'YYYYMMDD'),'YYYY.MM.DD')
This will interpret the DateInput field according to the input format, YYYYMMDD, and return it in the format you want to use, YYYY.MM.DD.
Function  Description 

Date  Date() formats an expression as a date using the format set in the system variables in the data load script, or the operating system, or a format string, if supplied. 
date_hash 
Date# evaluates an expression as a date in the format specified in the second argument, if supplied. 
Dual 
Dual() combines a number and a string into a single record, such that the number representation of the record can be used for sorting and calculation purposes, while the string value can be used for display purposes. 
Interval  Interval() formats a number as a time interval using the format in the system variables in the data load script, or the operating system, or a format string, if supplied. 
interval_hash  Interval#() evaluates a text expression as a time interval in the format set in the operating system, by default, or in the format specified in the second argument, if supplied. 
Money  Money() formats an expression numerically as a money value, in the format set in the system variables set in the data load script, or in the operating system, unless a format string is supplied, and optional decimal and thousands separators. 
money_hash 
Money#() converts a text string to a money value, in the format set in the load script or the operating system, unless a format string is supplied. Custom decimal and thousand separator symbols are optional parameters. 
Num  Num() formats a number, that is it converts the numeric value of the input to display text using the format specified in the second parameter. If the second parameter is omitted, it uses the decimal and thousand separators set in the data load script. Custom decimal and thousand separator symbols are optional parameters. 
num_hash 
Num#() interprets a text string as a numerical value, that is it converts the input string to a number using the format specified in the second parameter. If the second parameter is omitted, it uses the decimal and thousand separators set in the data load script. Custom decimal and thousand separator symbols are optional parameters. 
Text  Text() forces the expression to be treated as text, even if a numeric interpretation is possible. 
Time  Time() formats an expression as a time value, in the time format set in the system variables in the data load script, or in the operating system, unless a format string is supplied. 
time_hash 
Time#() evaluates an expression as a time value, in the time format set in the data load script or the operating system, unless a format string is supplied. 
Timestamp  TimeStamp() formats an expression as a date and time value, in the timestamp format set in the system variables in the data load script, or in the operating system, unless a format string is supplied. 
timestamp_hash  Timestamp#() evaluates an expression as a date and time value, in the timestamp format set in the data load script or the operating system, unless a format string is supplied. 
Numeric functions that can be used in a calculated field expression
You can use these functions to round numeric values.
Function  Description 

ceil  Ceil() rounds up a number to the nearest multiple of the step shifted by the offset number. 
div  Div() returns the integer part of the arithmetic division of the first argument by the second argument. Both parameters are interpreted as real numbers, that is, they do not have to be integers. 
even  Even() returns True (1), if integer_number is an even integer or zero. It returns False (0), if integer_number is an odd integer, and NULL if integer_number is not an integer. 
fabs  Fabs() returns the absolute value of x. The result is a positive number. 
fact  Fact() returns the factorial of a positive integer x. 
floor 
Floor() rounds down a number to the nearest multiple of the step shifted by the offset number. 
fmod  fmod() is a generalized modulo function that returns the remainder part of the integer division of the first argument (the dividend) by the second argument (the divisor). The result is a real number. Both arguments are interpreted as real numbers, that is, they do not have to be integers. 
frac  Frac() returns the fraction part of x. 
mod  Mod() is a mathematical modulo function that returns the nonnegative remainder of an integer division. The first argument is the dividend, the second argument is the divisor, Both arguments must be integer values. 
odd  Odd() returns True (1), if integer_number is an odd integer or zero. It returns False (0), if integer_number is an even integer, and NULL if integer_number is not an integer. 
round 
Round() returns the result of rounding a number up or down to the nearest multiple of step shifted by the offset number. 
sign  Sign() returns 1, 0 or 1 depending on whether x is a positive number, 0, or a negative number. 
Conditional functions that can be used in a calculated field expression
You can use these functions to evaluate a condition and then return different answers depending on the condition value.
Function  Description 

alt  The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used. 
class  The class function assigns the first parameter to a class interval. The result is a dual value with a<=x<b as the textual value, where a and b are the upper and lower limits of the bin, and the lower bound as numeric value. 
if  The if function returns a value depending on whether the condition provided with the function evaluates as True or False. 
match 
The match function compares the first parameter with all the following ones and returns the numeric location of the expressions that match. The comparison is case sensitive. 
mixmatch  The mixmatch function compares the first parameter with all the following ones and returns the numeric location of the expressions that match. The comparison is case insensitive. 
pick  The pick function returns the n:th expression in the list. 
wildmatch  The wildmatch function compares the first parameter with all the following ones and returns the number of the expression that matches. It permits the use of wildcard characters ( * and ?) in the comparison strings. * matches any sequence of characters. ? matches any single character. The comparison is case insensitive. 
NULL functions that can used in a calculated field expression
You can used these functions to return or detect null values.
Function  Description 

Null 
The Null function returns a NULL value. 
IsNull 
The IsNull function tests if the value of an expression is NULL and if so, returns 1 (True), otherwise 0 (False). 
Mathematical functions that can used in a calculated field expression
You can use these functions for mathematical calculations.
Function  Description 

e 
The function returns the base of the natural logarithms, e ( 2.71828...). 
rand 
The function returns a random number between 0 and 1. This can be used to create sample data. 
Exponential and Logarithmic functions that can used in a calculated field expression
You can use these functions for exponential and logarithmic calculations.
Function  Description 

exp 
The natural exponential function, e^x, using the natural logarithm e as base. The result is a positive number. 
log 
The natural logarithm of x. The function is only defined if x> 0. The result is a number. 
log10 
The common logarithm (base 10) of x. The function is only defined if x> 0. The result is a number. 
pow 
Returns x to the power of y. The result is a number. 
sqr 
x squared (x to the power of 2). The result is a number. 
sqrt 
Square root of x. The function is only defined if x >= 0. The result is a positive number. 
Distribution functions that can used in a calculated field expression
You can use these functions for statistical distribution calculations.
Function  Description 

ChiDist 
ChiDist() returns the onetailed probability of the chi^{2} distribution. The chi^{2} distribution is associated with a chi^{2} test. 
ChiInv 
ChiInv() returns the inverse of the onetailed probability of the chi^{2} distribution. 
FDist  FDist() returns the accumulated probability of the F distribution. 
FInv  FInv() returns the inverse of the accumulated probability of the F distribution. 
NormDist  NormDist() returns the cumulative normal distribution for the specified mean and standard deviation. If mean = 0 and standard_dev = 1, the function returns the standard normal distribution. 
NormInv  NormInv() returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. 
TDist  TDist() returns the probability for the student's t distribution where a numeric value is a calculated value of t for which the probability is to be computed. 
TInv  TInv() returns the t value of the student's t distribution as a function of the probability and the degrees of freedom. 
Geospatial functions that can used in a calculated field expression
You can use this function to handle geospatial data.
Function  Description 

GeoMakePoint 
GeoMakePoint() is used in scripts and chart expressions to create and tag a point with latitude and longitude. 
Color functions that can used in a calculated field expression
You can use these functions for setting and evaluating color properties.
Function  Description 

ARGB 
ARGB() is used in expressions to set or evaluate the color properties of a chart object, where the color is defined by a red component r, a green component g, and a blue component b, with an alpha factor (opacity) of alpha. 
HSL 
HSL() is used in expressions to set or evaluate the color properties of a chart object, where the color is defined by values of hue, saturation, and luminosity between 0 and 1. 
RGB  RGB() returns an integer corresponding to the color code of the color defined by the three parameters: the red component r, the green component g, and the blue component b. These components must have integer values between 0 and 255. The function can be used in expressions to set or evaluate the color properties of a chart object. 
Logical functions that can used in a calculated field expression
You can use these functions for handling logical operations.
Function  Description 

IsNum 
Returns 1 (True) if the expression can be interpreted as a number, otherwise 0 (False). 
IsText 
Returns 1 (True) if the expression has a text representation, otherwise 0 (False). 
System functions that can be used in a calculated field expression
You can use these functions for accerssing system ,device, and Qlik Sense app properties.
Function  Description 

systemfunctions 
This function returns a string containing the name of the user that is currently connected. It can be used in both the data load script and in a chart expression. 
ReloadTime 
This function returns a timestamp for when the last data load finished. It can be used in both the data load script and in a chart expression. 