Numeric Functions

Numeric functions are used for generic number calculations.

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 numeric functions in the data load editor
  • Working with numeric functions in visualisations

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

The numeric 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: Floor

The Floor() function rounds down the number to a specified increment.

Copy code 


floor(x[, step[, offset]])

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 x, [step] and [offset]. X is the value that you want to round down, [step] is the rounding interval and [offset] is the start of the interval.
  3. Anything between square brackets [] is an optional parameter. In this function, there are two optional parameters. Optional parameters do not have to be included but if you do, DO NOT include the brackets.

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

Copy code 


Floor(Num,1,0.5)

The resulting field would be rounded down a whole number but as the offset is 0.5, the result will always be .5. For example, the number 4.13 would round down to 3.5 as would 3.9.

How to use numeric functions in the data load editor

Here are some important points on using numeric 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
  • The functions are useful for a range of applications but the Floor() function is particularly useful for rounding down datetime fields.

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

Copy code 


SALES:
LOAD
    "Order ID",
    "DateTime",
    TimeStamp(Floor("DateTime")) as "Start of Day",
    "Amount"
    
FROM [sales.qvd] (qvd);

The newly created field “Start of Day” would contain the start of the day (midnight) for each order rather than the actual time of the sale.

How to use numeric functions in visualisations

At the time of writing, all general numeric functions can be used in visualisations in the same way as they are used in the data load editor.

  • The functions can be nested and combined with other types of functions.
  • The functions can be used within dimension or measure fields depending on your requirements.

Here is an example of using the numeric function Sign() to signify whether the customers’ spend has increased or decreased YoY. The formula used in the field ‘YoY Increase?’ is:

Copy code 


Sign(Sum({<[Date.Calendar.IsThisYear] = {1}>} Value) - Sum({<[Date.Calendar.IsLastYear] = {1}>} Value))