Script functions within the Qlik Sense app script are used to transform and aggregate data. This article will list the types of functions available and their purpose. Click on the button below each function type to see the full list of functions of this type.
Many functions can be used in both, the script and the charts identically. This particular page lists script functions only. See chart functions here. Note that there are many working Qlik Sense functions. Functions are subject to being updated or phased out. Please see the Qlik Sense help pages here for an up-to-date list of functions.
Aggregation Functions
Aggregation functions group multi-row data into a single row based on some grouping.
The functions are calculated by looping through the table rows and working out the result for each given group. Here are some examples of aggregation functions:
| Avg | This function returns the average of numeric values within a field for a group. |
| Count | This function returns the count of values within a field for a group. |
| Max | This function returns the max numeric value within a field for a group. |
| Min | This function returns the min numeric value within a field for a group. |
| Sum | This function returns the total of numeric values within a field for a group. |
See more on aggregation functions
Conditional Functions
Conditional functions evaluate specified conditions and return a result based on the outcome of the condition evaluation. For example, if a condition evaluates to outcome 1, return ‘a’; otherwise, return ‘b’. Here are some examples of conditional functions:
| Coalesce | This function returns the first non-NULL value of the expressions used within the function. |
| If | This function returns the value based on whether the condition evaluates to true or false. The return parameters are specified for both condition results. |
| Match | This function matches the first input value with all the subsequent ones and returns the position of the match. For example, if the value matched the second input, 2 will be returned. The matching is case-sensitive. |
| Pick | This function returns the nth value from the list given. The position of the value to be returned is given first followed by the list of values. Expressions can be used in the function. |
| WildMatch | This function matches the first input value with all the subsequent ones and returns the position of the match. For example, if the value matched the second input, 2 will be returned. The matching is case-insensitive. WildMatch permits the use of wildcard characters. |
See more on conditional functions
Counter Functions
Counter functions count records within a table to return results. Some do not require any input values at all. Here are some examples of counter functions:
| AutoNumber | This function returns a unique integer for each distinct value encountered. |
| RecNo | This function returns the number of data rows in the source data that is being returned. The numbering starts with 1. |
| RowNo | This function returns the number of data row in the resulting data table that is being returned. The numbering starts with 1. |
Date & Time Functions
These functions are used to transform the date and time values. Qlik Sense reads dates and processes these functions based on the numerical value of the date. The number represents days since the 30th December 1899. Here are some examples of date & time functions:
| AddYears | This function returns a date occurring n years before or after an input date specified. |
| Month | This function returns a dual value of the month of the given date. A dual value means that the month is represented visually as per the app parameters, usually in the format of ‘MMM’ yet Qlik Sense reads it as an integer for sorting purposes. |
| MonthStart | This function returns the timestamp of the month’s start to the first millisecond. |
| Today | This function returns the date of the current date time. Current being when a specified action last took place such as an app reload, app open or a function call. |
| Year | This function returns the integer of the year of a given date. |
See more on date & time functions
Exponential and Logarithmic Functions
These functions are used in calculations to transform data. Here are some examples of these functions:
| Exp | This function returns a result that is equivalent to the formula e^x, where e is the natural logarithm. |
| Log | This function returns a result that is the natural logarithm of the integer. |
| Sqr | This function returns a result to the formula x^2 (x squared). |
| Sqrt | This function returns the square root of the integer. |
See more on exponential & logarithmic functions
File Functions
These functions return information about the file currently being read by Qlik Sense as part of the script execution. Here are some examples of file functions:
| FileName | This function returns the name and extension of the file currently being read. The path is not included. |
| FilePath | This function returns the path to the file currently being read. The name and extension of the file are also included. |
| FileSize | This function returns the size of a specified file. The size is given in bytes. |
| FileTime | This function returns the last modified date time of a specified file. The date time is given in UTC time zone. |
Financial Functions
These functions are used to calculate payments and interest rates. Here are some examples of financial functions:
| Fv | This function returns the future value of an investment. |
| Pmt | This function returns the payment required for a loan given a specific interest rate and the number of periods. |
| Pv | This function returns the present value of an investment. |
See more on financial functions
Formatting Functions
These functions are used to format the way integers are displayed in visualisations. Here are some examples of formatting functions:
| Date | This function applies a date format to the field. |
| Money | This function formats a field as a monetary field. |
| Num | This function formats a field as a number. |
See more on formatting functions
Geospatial Functions
These functions are for handling data when using map visualisations. They apply to geospatial data. Some of the functions are aggregating functions. Here are some examples of geospatial functions:
| GeoBoundingBox | This function reads the coordinates in a field and returns coordinates that represent a rectangle area that fits all the given coordinates. |
| GeoMakePoint | This function returns tag points given the longitude and latitude given. |
| GeoProject | This function applies a projection to a geometry. |
See more on geospatial functions
Interpretation Functions
These functions are used to inform Qlik on how to read a string if it represents a numeric value. Here are some examples of interpretation functions:
| Date# | This function converts a string to a specific date format so that Qlik can interpret it as a date. |
| Money# | This function converts a string to a specific monetary format so that Qlik can interpret it as a monetary value. |
| Num# | This function converts a string to a specific numeric format so that Qlik can interpret it as a number. |
See more on interpretation functions
Inter-record Functions
These functions are used to tell Qlik to use or look at values from rows other than the one currently being read. Some functions can be used to look at values in other previously loaded tables. Here are some examples of inter-record functions:
| Exists | This function checks whether a specific value in a field has already been loaded. |
| FieldIndex | This function returns the position of a value in a specific field. |
| Peek | This function returns a value in a specific field in a given table and row number. |
| Previous | This function returns a value in the previous row. The result only includes rows that have not been excluded using a where clause. |
See more on inter-record functions
Logical Functions
These functions are used to check specific logical criteria. Here are some examples of logical functions:
| IsNum | This function checks whether the value is numeric or not. The function returns true (-1) or false (0). |
Mapping Functions
These functions are used to apply a previously loaded mapping table to a field. Doing so allows for mapping values from the mapping table to the field values. Here are some examples of mapping functions:
| ApplyMap | This function maps a previously loaded mapping table to a field’s values. |
Math Functions
These functions are used for mathematical applications. Here are some examples of math functions:
| E | This function returns the ‘e’ number, i.e. the base of the natural logarithm. |
| Pi | This function returns the value of Pi. |
| Rand | This function returns a random number between 0 and 1. |
NULL Functions
These functions are used for identifying or interacting with NULLs. Here are some examples of NULL functions:
| Null | This function returns a NULL value. |
Numeric Functions
These functions are general number functions. Here are some examples of numeric functions:
| Ceil | This function rounds up the number to the specified increment. |
| Div | This function returns the whole number part only of a result of a division. |
| Even | This function returns true (-1) if the integer is an even number or false (0) if it’s odd. |
| Floor | This function rounds down the number to the specified increment. |
| Odd | This function returns true (-1) if the integer is an odd number or false (0) if it’s even. |
Range Functions
These functions are used for producing a value using a range of values. For example, getting a max value out of multiple fields. Here are some examples of range functions:
| RangeAvg | This function returns the average of a range of values. |
| RangeCount | This function returns the count of a range of values. |
| RangeMax | This function returns the max of a range of values. |
| RangeMin | This function returns the min of a range of values. |
| RangeSum | This function returns the sum of a range of values. |
Statistical Distribution Functions
These functions are used for calculating the distribution of a given set of values. Here are some examples of these functions:
| BinomDist | This function returns the accumulated probability of the binomial distribution. |
| ChiDist | This function returns the accumulated probability of the chi2 distribution. |
| FDist | This function returns the accumulated probability of the f distribution. |
| NormDist | This function returns the normal distribution for the mean and standard deviation given. |
See more on statistical distribution functions
String Functions
These functions are used for transforming string values. Here are some examples of string functions:
| Capitalize | This function returns the string values in a capitalised form, i.e. upper case first letter of each word. |
| Index | This function returns the position of a specified substring within a string as an integer. |
| Len | This function returns the length of a string. |
| Replace | This function returns a string after replacing the specified substring with the required substring. |
| Upper | This function returns the string in upper case. |
System Functions
These functions give information about the environment and the app. Here are some examples of system functions:
| Author | This function returns the name of the app author. |
| DocumentName | This function returns the name of the Qlik app, including the extension. |
| DocumentPath | This function returns the path to the Qlik app. |
| DocumentTitle | This function returns the title of the Qlik app. |
| ReloadTime | This function returns the date and time of when the app was last reloaded. |
Table Functions
These functions give information about the data table being read. Here are some examples of table functions:
| NoOfFields | This function returns the number of fields in a table. |
| NoOfRows | This function returns the number of rows in a table. |
Trigonometric Functions
These functions are designed to perform trigonometric operations. Here are some examples of trigonometric functions:
| Cos | This function returns the cosine of the integer. |
| Sin | This function returns the sine of the integer. |
| Tan | This function returns the tangent of the integer. |
