Range Functions

Range functions produce a single value out of a range of values. For example, getting a max value out of multiple fields.

This article will cover the basic principles of working with these functions in the data load editor and visualisations, including:

  • The general function syntax
  • Working with range functions in the data load editor
  • Working with range functions in visualisations

For a complete, up-to-date list of range functions, please see the Qlik Sense help pages here.

The range function syntax

The general function syntax varies depending on the function. In this section, we will use an example function and decipher its syntax. This understanding will then help you read the syntax of other functions.

Example: RangeMax

The RangeMax() function returns the maximum value from a given set of values. The values can be located in different fields or provided as numbers.

Copy code 


RangeMax(first_expr[, Expression])

The rules for reading the function syntax are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, the elements are first_expr and [expression]. Both elements represent the numbers you want Qlik to get the maximum value from.
  3. Anything between square brackets [] is an optional parameter. In this function, the optional parameter is [expression]. This means that you have to provide one value as a minimum for the function to work but you can enter additional values. If optional parameters are included, you DO NOT write the square brackets around them.

An example of this function being used with the optional parameters included would be:

Copy code 


RangeMax("Last Login", "Last Email", "Last Phone Call")

The resulting field would return the maximum date value from the fields entered which would in this example represent the last date each customer interacted with the business.

How to use range functions in the data load editor

Here are some important points on using range functions in the data load editor:

  • All of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • The functions can be nested and combined with other types of functions
  • There are many types of range functions, including financial and statistical functions, so make sure to check out the Qlik help pages
  • The functions are particularly useful when you’re working with wide tables and you need to get max values from different fields

Here is an example of the RangeSum() function being used:

Copy code 


SALES:
LOAD
    "Order ID",
    "Customer ID",
    "Date",
    "Value",
    "VAT",
    RangeSum("Value", "VAT") as "Total Spend"
    
FROM [sales.qvd] (qvd);

The newly created field “Total Spend” includes the sum of fields “Value” and “VAT” to give the total amount the customer spent on their order.

How to use range functions in visualisations

At the time of writing, all range functions can be used in visualisations.

  • The functions can be nested and combined with other types of functions.
  • There are many types of range functions, including financial and statistical functions, so make sure to check out the Qlik help pages
  • The functions can be used in dimensions or measures depending on your requirement.

Here is an example of using the RangeSum() function within a measure to calculate the total customer spend. The formula used in the final column of the table is:

Copy code 


RangeSum(Sum(Value),Sum(VAT))