Qlik Sense General Numeric Functions With Syntax

1. Objective

In our last Qlik Sense Tutorial, we discussed Qlik Sense System Functions. Today, we will see Qlik Sense General Numeric Functions. So, the general numeric functions, as the name suggests are all the functions used to apply basic mathematical operations on the numeric values. In order to explain these functions, we have used ‘x’ which represents any real number value. Also, we will see combination and permutation functions, modulo, parity and rounding functions.

Qlik Sense General Numeric Functions With Syntax

Qlik Sense General Numeric Functions With Syntax

So, let’s start Qlik Sense General Numeric Functions tutorial.

2. General Numeric Functions in Qlik Sense

General Numeric Functions in Qlik Sense

General Numeric Functions in Qlik Sense

i. Bitcount

The bitcount() function returns the number of 1s present in the binary equivalent of a given number. The integer value to be evaluated in this function is a 32-bit value or integer.

The syntax for Bitcount Qlik Sense General Numeric Functions:

BitCount(integer_number)

For instance,

BitCount( 3 ) returns 2 because the binary representation of the number 3 is 101, thus having two 1s.

BitCount( -1 ) returns 64 because the binary representation of -1 has 64 1s.

Recommended Reading – Qlik Sense Field Functions

ii. Div

This function returns the integer part of the result of the division of two given parameters. The two parameters (dividend and divisor) are real numbers but this function will return only the integer part of the answer of the division taking place.

The syntax for Div Qlik Sense Generic Numeric Functions:

Div(integer_number1, integer_number2)

Where integer_number1 is the dividend and integer_number2 is the divisor.

For example,

Div( 7,2 ) returns 3 which is only the integer part out of the actual result 3.5.

Div( 7.1,2.3 ) returns 3 as the integer part.

Div( 6,3 ) returns 2.

Div( -4,3 ) returns -1 as the integer part of the actual answer.

Div( 4,-3 ) returns -1 as the integer part of the actual answer.

Div( -4,-3 ) returns 1 as the integer part of the actual answer.

iii. Fabs

This function returns the absolute value of a value which is a real number. Result of this function is always a positive number. The input value is represented as x in the syntax.

The syntax for Fabs Qlik Sense Numeric Functions:

fabs(x)

For instance,

fabs(18.5) will return 18.5.

fabs(-2.5) will return 2.5.

iv. Fact

This function calculates the factorial for a given value, x, which must be an integer. If the input value is not an integer, then the number will be truncated and made an integer. Also, if a value is negative, then this function will return NULL.

You must check Qlik Sense Table Functions

The syntax for Fact Qlik Sense Numeric Functions:

fact(x)

For example,

fact(1) will return 1 as the factorial of 1.

fact(5) will return 120 ( 1 * 2 * 3 * 4 * 5 = 120 ) as the factorial of 5.

fact(-5) returns NULL because the value is negative.

v. Frac

This function returns the fraction of the input value x. The fraction of a value x is calculated by Frac(x) – Floor(x) = x. Where Frac(x) is the original value and Floor(x) is the integer part of the input value.

The syntax for Frac Qlik Sense Numeric Functions:

Frac(x)

For example,

Frac(12.43)returns 0.43 derived from the calculation, 12.43 – 12 = 0.43

Frac(-1.4) returns 0.6 derived from the calculation, as -1.4 is a negative value, the Floor(-1.4) = -2. So, the result will be produced following the calculation as given, -1.4 = 1.4 – (-2) = -1.4 + 2 = 0.6

vi. Sign

This function returns the sign associated with a value i.e. 1, 0, or -1. This tells whether the input value, x, is a positive number, negative number or a zero.

The syntax for Qlik Sense Sign Numeric Function:

Sing(x)

For example,

Sign(60) returns 1 as 60 is a positive number.

Sign(0) returns 0.

Sign(- 234) returns -1 as the input value is negative.

3. Combination and Permutation Functions in Qlik Sense

i. Combin

This function evaluates the number of combinations of a selected number of q elements from the given set of p elements. The function evaluates the parameters based on the formula of combination, Combin(p,q) = p! / q!(p-q)!

We recommend you to read Qlik Sense Financial Functions

The syntax for Combin Qlik Sense Numeric Functions:

Combin(p, q)

For example,

Combin(35,7) returns 6,724,520 as the result of the number of combination can be made of 7 numbers being selected from a total of 35 lottery numbers.

If the value of p and q are not integers, then the values are truncated and made integers.

ii. Permut

This function evaluates the permutations possible for q elements from the set of p elements. The function follows the formula for calculating the permutation i.e. Permut(p,q) = (p)! / (p – q)!

The syntax for Permut Qlik Sense Numeric Functions:

Permut(p,q)

For instance,

Permut(8,3) returns 336 as an answer to the question, that in how many ways could the three medals, gold, silver and bronze be distributed after a 100 m final in 8 participants? Where, p is 8 and q is 3.

4. Modulo Functions in Qlik Sense

i. Fmod

This function is known as a generalized modulo function which returns the remainder of division taking place between two parameters or arguments. In the division, the dividend (a) is divided by the devisor (b). All the values, input and output are real values and not integers.

Let’s revise Qlik Sense Interpretation Functions 

Syntax:

Fmod(a,b)

For example, the following sample values used in the functions return the remainder values.

fmod( 7,2 ) returns 1
fmod( 7.5,2 ) returns 1.5
fmod( 9,3 ) returns 0
fmod( -5,4 ) returns -1
fmod( 5,-4 ) returns 1
fmod( -5,-4 ) returns -1

ii. Mod

A little different from the fmod() function, the mod() function is the mathematical modulo function. This function takes in only integers as dividends and divisor and returns the remainder values which are positive and integers.

Syntax:

Mod(integer_number1, integer_number2)

For example, the following sample values will be evaluated by the function and returns the remainder as given below,

Mod(7,2) returns 1 as the remainder.
Mod(7.5,2) returns NULL because the dividend is not an integer.
Mod(9,3) returns 0 as it will be the remainder.
Mod(5,-4) returns NULL because divisor is a negative value.
Mod(-5,-4) returns NULL because both the parameters are negative.

5. Parity Functions in Qlik Sense

i. Even

This function evaluates a value for whether the given value is an even value or not. If the number is even or zero, then the function returns True (-1) and if it is not even, then the function returns False(0). It only takes in integer values and if the value if not an integer then it returns NULL.

Recommend Reading – Qlik Sense Day Numbering Functions

Syntax:

Even(integer_number)

For example, the given sample statements below will be checked by this function for being even or not.

Even(3) returns 0, False because 3 is an odd number.
Even(2 * 10) returns -1, True because the multiplication will evaluate to 20 which is even.
Even(3.14) returns NULL because 3.14 is not an integer.

ii. Odd

This function evaluates a value for whether the given value is an odd value or not. If the number is odd, then the function returns True (-1) and if it is not odd, then the function returns False(0). It only takes in integer values and if the value if not an integer then it returns NULL.

Syntax:

Odd(integer_number)

For example, the given sample statements below will be checked by this function for being odd or not.

Even(3) returns -1, True because 3 is an odd number.

Even(2 * 10) returns 0, False because the multiplication will evaluate to 20 which is even.

Even(3.14) returns NULL because 3.14 is not an integer.

6. Rounding Functions in Qlik Sense

Qlik Sense General Numeric Functions

Qlik Sense General Numeric Functions – Rounding Functions

i. Ceil

The ceil() function rounds up a given value to the nearest multiple of the number given shifting it by the offset number mentioned in the function.

You must learn Qlik Sense Mapping Functions

Syntax:

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

Where, x is the original input number.
step is the parameter where you set the nearest multiple to which the x should be rounded up. The default is set to be 1. It is known as interval increment. The intervals are counted as …0 < x <=1, 1 < x <= 2, 2< x <=3, 3< x <=4… (for instance).
offset is the parameter which sets the base of the step interval. The value set by default is 0.

For example,

Ceil(2.4 ) Returns 3 as the size of the step is 1 and the base of the step interval is 0.

Ceil(4.2 ) returns 5

Ceil(3.88 ,0.1) returns 3.9 where, the size of the interval is 0.1 and the base of the interval is 0.

ii. Floor

As opposed to the Ceil() function, the floor() function rounds down the value to the nearest multiple falling prior to the integer in the value shifting it by the offset number.

Syntax:

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

Where, x is the original input number.
step is the parameter where you set the nearest multiple to which the x should be rounded up. The default is set to be 1. It is known as interval increment. The intervals are counted as …0 < x <=1, 1 < x <= 2, 2< x <=3, 3< x <=4… (for instance).
offset is the parameter which sets the base of the step interval. The value set by default is 0.

For example,

floor(2.4 ) returns 2 as the size of the step is 1 and the base of the step interval is 0.

floor(4.2 ) returns 4.

floor(3.88 ,0.1) returns 3.8 where, the size of the interval is 0.1 and the base of the interval is 0 .

iii. Round

This function rounds a value up or down to the nearest integer shifting it by an offset number. If a value is in decimal like 2.5 which is exactly a middle value, then the function will round it upwards.

Syntax:

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

Where, x is the original input number.
step is the parameter where you set the nearest multiple to which the x should be rounded up. The default is set to be 1. It is known as interval increment. The intervals are counted as …0 < x <=1, 1 < x <= 2, 2< x <=3, 3< x <=4… (for instance).
offset is the parameter which sets the base of the step interval. The value set by default is 0.

Have a look at Qlik Sense Time Zone Function

For example,

round(2.5 ) returns 3 as it is rounded up.

round(4.2 ) returns 4. Rounded down.

round(3.88 ,0.1) returns 3.9 where, the size of the interval is 0.1 and the base of the interval is 0 .

So, this was all in Qlik Sense General Numeric Functions. Hope you like our explanation.

7. Conclusion

Hence, these were all the important general numeric functions used in applying crucial numeric operations on the numeric values used in the script. As we saw, these functions are divided into categories like combination and permutation functions, rounding up functions, parity functions etc., based on the purpose they are used for.

See also – 

Qlik Sense Counter Functions

Reference for Qlik Sense 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.