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 3 (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 drop-down 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 inter-record 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 (left-most) 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 non-recursive and works from left to right. |
| Right |
Right() returns a string consisting of the last (right-most) 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 date-time 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 (Monday-Friday) 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 system clock and Windows time 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 finds if timestamp lies inside the lunar week containing base_date. Lunar weeks in Qlik Sense are defined by counting 1 January as the first day of the week. |
| 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 1 January as the first day of the week. |
| 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, bi-month, quarter, tertial, or half-year 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, bi-month, quarter, tertial, or half-year 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 (Monday-Friday) 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 from the system clock for a specified time zone. |
| lunarweekend | This function returns a value corresponding to a timestamp of the last millisecond of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting 1 January as the first day of the week. |
| 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 1 January as the first day of the week. |
| lunarweekstart | This function returns a value corresponding to a timestamp of the first millisecond of the lunar week containing date. Lunar weeks in Qlik Sense are defined by counting 1 January as the first day of the week. |
| 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 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 1-12. 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, bi-month, quarter, tertial, or half-year 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, bi-month, quarter, tertial, or half-year containing a base date. |
| monthsstart | This function returns a value corresponding to the timestamp of the first millisecond of the month, bi-month, quarter, tertial, or half-year containing a base date. It is also possible to find the timestamp for a previous or following time period. |
| 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 (Monday-Friday) 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 from the system clock. The default 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 name of the current time zone, as defined in Windows. |
| today | This function returns the current date from the system clock. |
| 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 0-6 corresponding to the nominal day of the week (0-6). |
| weekend | 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 (Monday) 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 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 four-digit 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# |
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# | 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# |
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 an expression numerically in the number format set in the system variables in the data load script, or in the operating system, unless a format string is supplied, and optional decimal and thousands separators. |
| Num# |
Num#() converts a text string to a numerical value, in the number format set in the data load script or the operating system. 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# |
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# | 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 non-negative 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. |
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 one-tailed probability of the chi2 distribution. The chi2 distribution is associated with a chi2 test. |
| CHIINV |
CHIINV() returns the inverse of the one-tailed probability of the chi2 distribution. |
| FDIST | FDIST() returns the F-probability distribution. |
| FINV | FINV() returns the inverse of the F-probability 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() 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 values between 0 and 255. |
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 |
|---|---|
| OSUser |
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. |
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!