Math Functions (MathUtils.xml)

This provides some additional math functions.

Unless specified, the functions listed below will be added to the Math category.

Unit tests for all these functions in MathUtils.Test folder.

Int

Syntax: INT(Number)

Examples:

• INT(1.25) returns 1
• INT(-2.25) returns -2

Rounds a number to the nearest integer which is equal to it or closer to 0.

Modulo

Syntax: MODULO(Number, Divisor)

Examples:

• MODULO(1, 2) returns 1
• MODULO(2.25, 1.5) returns 0.75

Version of the MOD function allowing for floating point numbers.

• If either Modulo or Divisor are NULL, returns NULL.
• If Divisor is 0, then result will be NULL.

Sign

Syntax: SIGN(Number)

Examples:

• SIGN(1) returns 1
• SIGN(-3.4) returns -1

Determines the SIGN of the input.

• If either Modulo or Divisor are NULL, returns NULL.
• If Divisor is 0, then result will be NULL.

HexBinX

Added to Spatial category

C++ Function - HexBinX

Syntax: HEXBINX(PointX, PointY, Radius)

Examples:

• HEXBINX(0, 0) returns 0
• HEXBINX(1, 1) returns 1.5
• HEXBINX(1, 1, 2) returns 0

Given a point (X, Y), returns the X co-ordinate of the center of hexagonal bin containing the point.

• An optional third parameter, allows the size of the Hexagon to be changed (defaults to 1).
• Reproduces Tableau HEXBINX function.
• Based on the d3.js implementation but rotated by 90 degrees.
• If PointX or PointY is NULL, returns NULL.
• If Radius is NULL, uses 1.

HexBinY

Added to Spatial category

C++ Function - HexBinY

Syntax: HEXBINY(PointX, PointY, Radius)

Examples:

• HEXBINY(0, 0) returns 0
• HEXBINY(1, 1) returns 0.866025
• HEXBINY(1, 1, 2) returns 0

Given a point (X, Y), returns the Y co-ordinate of the center of hexagonal bin containing the point.

• An optional third parameter, allows the size of the Hexagon to be changed (defaults to 1).
• Reproduces Tableau HEXBINY function.
• Based on the d3.js implementation but rotated by 90 degrees.
• If PointX or PointY is NULL, returns NULL.
• If Radius is NULL, uses 1.

RAND_TRIANGULAR

Syntax: RAND_TRIANGULAR(P, Min, Mode, Max)

Examples:

• RAND_TRIANGULAR(0, 0, 1, 2) returns 0
• RAND_TRIANGULAR(1, 0, 1, 2) returns 2
• RAND_TRIANGULAR(0.5, 0, 1, 2) returns 1

Given a probability, P, convert into the value on a Triagular distribution with range (Min, Max) and mode equal to Mode.

• If P is less than 0 or greater than 1, returns NULL.
• Use the Rand() function to generate a random value of P.

Avg

C++ Function - Average

Syntax: AVG(Value1, Value2, ...) Takes variable number of parameters.

Examples:

• AVG(1, 2) returns 1.5
• AVG(1, NULL) returns 1
• AVG(NULL, 2) returns 2
• AVG(1, 2, 3) returns 2

Computes the average (mean) of a set inputs ignoring NULL values.

• All parameters must be a numeric type.
• Minimum of 2 parameters, but can be as many as you need.
• If all parameters are null, returns NULL.

Count

C++ Function - Count

Syntax: COUNT(Value1, Value2, ...) Takes variable number of parameters.

Examples:

• COUNT(1, 2) returns 2
• COUNT(1, NULL) returns 1
• COUNT(NULL, 2) returns 1
• COUNT(1, 2, 3) returns 3
• COUNT('A', NULL, 1, 'B') *returns* 3`

Counts the number of values which are not NULL.

• Can be any combination of numeric or text types.
• Minimum of 2 parameters, but can be as many as you need.
• If all parameters are null, returns 0.

Sum

C++ Function - Sum

Syntax: SUM(Value1, Value2, ...) Takes variable number of parameters.

• SUM(1, 2) returns 3
• SUM(1, NULL) returns 1
• SUM(NULL, 2) returns 2
• SUM(1, 2, 3) returns 6

Computes the total of a set inputs ignoring NULL values.

• All parameters must be a numeric type
• Minimum of 2 parameters, but can be as many as you need.
• If all parameters are null, returns 0.

Deg

Syntax: DEG(Radians)

• DEG(0) returns 0
• DEG(1) returns 57.29577951
• DEG(NULL()) returns NULL

Converts from radians to degrees.

Rad

Syntax: RAD(degrees)

• RAD(0) returns 0
• RAD(57.29577951) returns 1
• RAD(NULL()) returns NULL

Converts from degrees to radians.

NormDist

C++ Function - NormDist

Syntax: NORMDIST(X, Mean, StDev, Cumulative) Defaults: Mean = 0, StDev = 1, Cumulative = false

Computes either probability mass function or cumulative distribution function value of x on a Normal distribution with specified Mean and Standard Deviation.

NormInv

C++ Function - NormInv

Syntax: NORMINV(P, Mean, StDev) Defaults: Mean = 0, StDev = 1

Computes the inverse of cumulative distribution function value for a specified value of P on a Normal distribution with specified Mean and Standard Deviation.

LogNormDist

C++ Function - LogNormDist

Syntax: LOGNORMDIST(X, Location, Scale, Cumulative) Defaults: Location = 0, Scale = 1, Cumulative = false

Computes either probability mass function or cumulative distribution function value of x on a Log Normal distribution with specified Location and Scale.

LogNormInv

C++ Function - LogNormInv

Syntax: LOGNORMINV(P, Location, Scale) Defaults: Location = 0, Scale = 1

Computes the inverse of cumulative distribution function value for a specified value of P on a Log Normal distribution with specified Location and Scale.

TDist

C++ Function - TDist

Syntax: TDIST(X, DegreesOfFreedom)

Computes the two-tailed probability on a student T distribution with specified degrees of freedom for a value of x.

TInv

C++ Function - TInv

Syntax: TINV(P, DegreesOfFreedom)

Computes the inverse of cumulative distribution function value for a specified value of P on the two tailed student T distribution with specified degrees of freedom.

ChiDist

C++ Function - ChiDist

Syntax: CHIDIST(X, DegreesOfFreedom)

Computes the right-tailed probability on a Chi-Squared distribution with specified degrees of freedom for a value of x.

ChiInv

C++ Function - ChiInv

Syntax: CHIINV(P, DegreesOfFreedom)

Computes the inverse of the right-tailed probability for a specified value of P on the Chi-Squared distribution with specified degrees of freedom.

Phi

Syntax: PHI()

Examples:

• PHI() returns 2.61803398875

Returns the golden ratio constant.

GammaDist

C++ Function - GammaDist

Syntax: GAMMADIST(X, Shape, Scale, Cumulative) Defaults: Scale = 1, Cumulative = false

Computes either probability mass function or cumulative distribution function value of x on a Gamma distribution with specified Shape (alpha) and Scale (beta).

GammaInv

C++ Function - GammaInv

Syntax: GAMMAINV(P, Shape, Scale) Defaults: Scale = 1

Computes the inverse of cumulative distribution function value for a specified value of P on a Gamma distribution with specified Shape (alpha) and Scale (beta).

PoissonDist

C++ Function - PoissonDist

Syntax: POISSONDIST(X, Mean, Cumulative) Defaults: Cumulative = false

Computes either probability mass function or cumulative distribution function value of x on a Poisson distribution with specified Mean.

PoissonInv

C++ Function - PoissonInv

Syntax: PoissonINV(P, Mean)

Computes the inverse of cumulative distribution function value for a specified value of P on a Poisson distribution with specified Mean. Note that we're using the integer round up methodology as this seemed to match the expected value in this case.

FDist

C++ Function - FDist

Syntax: FDIST(X, DegreesOfFreedom1, DegreesOfFreedom2)

Computes the right tailed probability on an F distribution with specified degrees of freedom for a value of x.

FInv

C++ Function - FInv

Syntax: FINV(P, DegreesOfFreedom1, DegreesOfFreedom2)

Computes the inverse of cumulative distribution function value for a specified value of P on the right tailed F distribution with specified degrees of freedom.