Math Functions and Operators

Combine multiple base measures and use a broad range of math operators and functions to derive new calculations.

Math Operators

For example, to define a Revenue measure based on the two existing  measures Sum: Quantity and Avg: Unit Price enter the following expression:

[Order_Date: Sum: Quantity]*[Order_Date: Avg: Unit Price]

BQL supports standard operators including:

  • = equals
  • * times
  • + plus
  • / divided by
  • - minus
  • % modulo
  • < less than
  • > greater than
  • <> not equal to
  • <= less than or equal to
  • >= greater than or equal to
  • AND
  • OR
  • LIKE
  • NOT LIKE
  • IS NULL
  • IS NOT NULL

Math Functions

BQL provides functions for advance mathematics.

Absolute Value: ABS

ABS provides the absolute value of a measure value.

Syntax:

ABS([measure])

Example:

SELECT ABS([Order_Date: Sum: Quantity]) FROM [ALL]

Arccosine: ARCCOS

The ARCCOS function returns the arccosine of a value between ο and π.

ARCCOS([logical_column])

Arcsine: ARCSIN

Returns the arcsine of a number between -π/2 and π/2.

ARCSIN([logical_column])

Arctangent: ARCTAN

Returns the arctangent of a number between -π/2 and π/2.

ARCTAN([logical_column])

Arctangent of two: ATAN2

Returns the arctangent of x and y.

ATAN2(x,y)

Rounding Up: CEILING

CEILING rounds up to the closest integer greater than or equal to the given number.

CEILING([measure])

Cosine: COS

Returns the cosine of a given angle.

COS([logical_column])

Radians to degrees: DEGREES

Converts x radians to degrees.

DEGREES(x)

Exponent: EXP

Returns e raised to the power of the number given.

EXP([logical_column])

EXP([OrderDate: Sum: Quantity])

Rounding Down: FLOOR

FLOOR rounds down to the closest integer less than or equal to a given number.

FLOOR([logical_column])

FLOOR([OrderDate: Sum: Quantity])

Natural Logarithm: LN

Returns the natural logarithm of a given number in a specified base.

LOG([measure | numeric_attribute])

Log Base e: LOG

Log base e of X.

Returns the logarithm of a given number in a specified base.

LOG([measure | numeric_attribute])

Log Base 10: LOG10

Log base 10 of x.

LOG10([measure | numeric_attribute])

Middle Value: MEDIAN

MEDIAN returns the value in the middle of all measure values at the report grain. It supports an optional BY break by attribute.

Important: MEDIAN is not supported for use in the input statement of a scripted data source (ETL).

MEDIAN supports breaking attributes that instruct the query engine to re-calculate the median based on values changing for a particular attribute.

MEDIAN([measure] BY [attribute])

For example:

SELECT [Products.Categories], MEDIAN([Order_Date: Sum: Revenue] BY [Products.Categories]) FROM [ALL]

Categories MEDIAN([Order_Date: Sum: Revenue], BY)
Desktops 150,413,715
Mobile Phones 101,721,700
MP3 Players 27,047,423
Notebooks 180,664,684
Tablets 204,825,336

PI

Returns Pi (3.14159265…).

PI()

Power: POW

POW raises the power of the first argument (x) to the power of the second argument (y).

POW(x,y)

POW([measure | numeric_attribute],[measure | numeric_attribute])

Running Sum: RSUM

Use RSUM to traverse the current result set to do running sum window based calculations. For example, the following RSUM formula requires a window size and calculates the trailing sum of values at the current report grain for the window provided.

RSUM(window_size_integer, [measure])

For example, using Northwind data, show the running sum for different values of last N, showing that N is just the same as the measure itself.

Signum: SIGN

The SIGN function returns +1 for x > 0, 0 for x == 0, and -1 for x < 0.

SIGN([measure | numeric_attribute])

Sine: SIN

The SIN function returns the sine of a given angle.

SIN([measure | numeric_attribute])

Square Root: SQRT

The SQRT function returns the square root of a measure.

SQRT([measure])

Tangent: TAN

The TAN function returns the tangent of a given angle.

TAN([measure | numeric_attribute])