QlikWorld 2020 Global Conference. Join us to discover how to get the most from your data. Act fast. Register now and save.

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:

  1. Click Add field and select Calculated field.

    The editor for Add calculated field opens.

  2. Type the name of the calculated field in Name.
  3. 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.

  4. 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:

  1. Select Edit from the drop-down menu next to the field name.

    The editor for Update calculated field opens.

  2. Edit the name of the calculated field in Name if you want to change it.
  3. Edit the expression of the calculated field.

  4. 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.

String functions
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 .

Date functions
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.

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.

Formatting and interpretation functions
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 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_hash

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_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.

Numeric functions
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.

Conditional functions
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.

NULL functions
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.

Mathematical functions
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.

Exponential and Logarithmic functions
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.

Distribution functions
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.

Geospatial functions
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.

Color functions
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.

Logical functions
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.

System functions
Function Description
system-functions

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 information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?