Dashboard Functions Reference

About the Dashboard Functions Reference

This topic describes all the functions you can use in Sisense's dashboard formula editor.

Note:

For information about the functions for custom tables and fields, see Data Functions for Custom Tables and Fields.

Functions in this reference are organized in to sections as follows:

Most of the functions have one or more of the following properties and are so marked:

  • Aggregative functions:

    Aggregative functions operate on a table column and typically return a numeric value. They are marked with (A) next to their names. Most of the statistical functions are aggregative.

  • Multipass compatible functions:

    Multipass compatible functions are marked with (M) next to their names. The Multipass Declaration - ([Dimension], Aggregation) is a group by statement that is used with aggregative functions to group the result set by one column and return a list. For example, the formula AVG([Brand], SUM([Cost])) calculates the average of total cost per brand.

  • Functions only supported for ElastiCubes:

    These functions are marked with (EC) next to their names. All other functions are supported for both ElastiCube and Live models.

Convention:

Function names are not case sensitive. For example, the average function can be entered as Avg, avg etc. The function selector in the dashboard editor always supplies function names in upper case for example, AVG. The upper case convention will be followed below.

Important note:

The Analytical Engine requires that every measure defined in the formula editor be aggregative. For example, instead of DDiff([Discharge Time], [Admission Time]), use AVG(DDIFF([Discharge Time], [Admission Time])).

The examples here do not always include a wrapping aggregation function, even though one will be required by the formula editor. (The example screen shots do include a wrapping aggregation function - because otherwise, they wouldn't work.)

Statistical Functions

Statistical Functions Supported by ElastiCube and Live Models

Aggregative Functions

The basic form calculates the average of a given database column (aggregation). The (M) form calculates the average of a given table column grouped by another field.

Syntax (Basic form)

AVG(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

AVG([Score])

Returns the average (mean) of the given Score column.

Syntax (Multipass form)

AVG(<group-by field>, <aggregation_fn>(<numeric field>))

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation_fn> Aggregation function (such as an average, sum, or minimum)
<numeric field> Any database column containing numeric values

Example:

AVG([Product], SUM([Sales]))

Returns the average (mean) of the total sales per product.

Notice that <aggregation_fn> here, is SUM.

The basic form counts the number of unique values within the given values. The (M) form Returns the number of unique <aggregation> values from all aggregated <group-by-field> members.

Syntax (Basic form)

COUNT(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

COUNT([Category ID])

Returns the number of different category IDs within the given list of items.

Syntax (Mutipass form)

COUNT( <group-by-field>, <aggregation> )

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation> Aggregation column

Returns the actual item count of the given list of items, including duplicates.

Syntax

DUPCOUNT(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

DUPCOUNT([Category ID])

Returns the actual count of category IDs in the list of items.

Returns the k-th largest value in a field.

Syntax

LARGEST(<numeric field>, <k>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<k> A number k, to select the kth largest value in the list, <numeric field>

Example:

LARGEST([Total Sales], 3)

Returns the third-largest Total Sales value.

The basic form returns the maximum value in a numeric field column. The (M) form returns the maximal <aggregation> value from all aggregated <group by field> members.

Syntax (Basic form)

MAX(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

MAX([Total Revenue])

Returns the item with the maximum Total Revenue.

Syntax (Multipass form)

MAX(<group-by-field>, <aggregation>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation> Aggregation column

Calculates the median of the given values. The median of a set of data is the middlemost number in the set. The median is also the number that is halfway into the set.

Syntax

MEDIAN(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

MEDIAN([Total Revenue])

Returns the item whose Total Revenue is the middlemost number in the set.

The basic form returns the minimum value in a numeric field column. The (M) form returns the minimal <aggregation> value from all aggregated <group by field> members.

Syntax (Basic form)

MIN(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

MIN([Total Revenue])

Returns the item with the minimum Total Revenue.

Syntax (Multipass form)

MIN(<group-by-field>, <aggregation>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation> Aggregation column

Returns the most frequently occurring value from a column.

Note:

If there is more than one mode value, the Mode function returns one of them randomly.

Syntax

MODE(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

MODE([Country ID])

Returns the country ID that is the most frequently occurring in the list of items.

Non-aggregative Functions

Calculates the percentage of the total.

Syntax

CONTRIBUTION(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

CONTRIBUTION([Total Sales])

Returns the percentage of total sales per group (e.g., per day or per product) out of total sales (for all days or all products).

The basic form returns the k-th percentile value from the given field, where k is any number between 0..1 (inclusive). The (M) form returns the k-th percentile value from the given numeric expression per <group by field>.

Syntax (Basic form)

PERCENTILE(<numeric field>, <k>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<k> Any number between 0...1 (inclusive) to indicate percentiles

Example:

PERCENTILE([Total Sales], 0.9)

Returns the 90th percentile of Total Sales.

Syntax (Multipass form)

PERCENTILE(<group-by-field>, <aggregation>, <k>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation> Aggregation column
<k> Any number between 0...1 (inclusive) to indicate percentiles

Example:

PERCENTILE([Brand],[Total Sales], 0.9)

Returns the 90th percentile of Total Sales grouped by Brand.

The basic form returns the k-th quaetile value from the given field, where k is any number between 0..4 (inclusive). It can return minimum value, first quartile, second quartile, third quartile, and max value.

The (M) form returns the k-th percentile value from the given numeric expression per <group by field>.

Syntax (Basic form)

QUARTILE(<numeric field>, <k>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<k>

Use these values to indicate the quartile:

  • k = 0 returns the Minimum value
  • k = 1 returns the first quartile (25th percentile)
  • k = 2 returns the Median value (50th percentile)
  • k = 3 returns the third quartile (75th percentile)
  • k = 4 returns the Maximum value

Example:

QUARTILE([Total Sales], 1)

Returns the first quartile of Total Sales.

Syntax (Multipass form)

QUARTILE(<group-by-field>, <aggregation>), <k>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation> Aggregation column
<k>

Use these values to indicate the quartile:

  • k = 0 returns the Minimum value
  • k = 1 returns the first quartile (25th percentile)
  • k = 2 returns the Median value (50th percentile)
  • k = 3 returns the third quartile (75th percentile)
  • k = 4 returns the Maximum value

Example:

QUARTILE([Brand],[Total Sales], 1)

Returns the first quartile of Total Sales grouped by Brand.

Returns the rank of a value in a list of values.

Syntax

RANK(<numeric field>, [DESC/ASC], [Rank Type], [<group-by field 1>,... , <group-by field n>])

Arguments

Argument Description
<numeric field> Any database column containing numeric values, or any calculation returning numeric values
[DESC/ASC] Optional. By default, sort order is ascending
[rank type]

Optional. Use these values to select ranking type:

  • 1224 - standard competition ranking (this is the default if no rank type is selected)
  • 1334 - modified competition ranking (supported only in ElastiCubes)
  • 1223 - dense ranking
  • 1234 - ordinal ranking
[<group-by field 1>,... , <group-by field n>] The group-by field must be presented in the widget

Example:

RANK([Total Cost], "ASC", "1224", [Product], [Years])

The rank of the total annual cost per each product, sorted in ascending order.

Returns the Standard Deviation of the given values (Population). Standard deviation is the square root of the average squared deviation from the mean. The standard deviation of a population gives researchers the amount of dispersion of data for an entire population.

Syntax

STDEVP(<numeric field>)

Arguments

Argument Description

<numeric field>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.

Example:

STDEVP([Cost])

Returns the Standard Deviation of the given values in the population.

Returns the Standard Deviation of the given values in a sample. Standard deviation is the square root of the unbiased average squared deviation from the mean. A standard deviation of a sample estimates the amount of dispersion in a given data set, based on a random sample.

Syntax

STDEV(<numeric field>)

Arguments

Argument Description

<numeric field>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.

Example:

STDEV([Cost])

Returns the Standard Deviation of the given values in the sample.

Returns the Variance of the given values (Population). Variance is the average squared deviation from the mean, based on an entire population of survey respondents.

Syntax

VARP(<numeric field>)

Arguments

Argument Description

<numeric field>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.

Example:

VARP([Cost])

Returns the Standard Deviation of the given values in the population.

Returns the Variance of the given values (Sample). Variance is the average squared deviation from the mean, based on a random sample of population of survey respondents.

Syntax

VARP(<numeric field>)

Arguments

Argument Description

<numeric field>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.

Example:

VAR([Cost])

Returns the Standard Deviation of the given values in the population.

Statistical Functions Supported by ElastiCube Only

Aggregative Functions

The basic form returns the correlation coefficient of two numeric fields. The (M) form returns the correlation coefficient of two fields aggregations grouped by another field.

Syntax (Basic form)

CORREL(<numeric field a>, <numeric field b>)

Arguments

Argument Description
<numeric field a>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.
<numeric field b>

Example:

CORREL([Revenue], [Cost])

Returns the correlation between revenue and cost.

Syntax (Multipass form)

CORREL(<group-by field>, <aggregation a>, <aggregation b>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation a> Aggregation function (such as an average, sum, or minimum) of a numeric field
<aggregation b> The same aggregation function on another numeric field

Example:

CORREL([Products], AVG([Revenue]), AVG([Cost]))

Returns the correlation between the average of revenue and cost per product.

The basic form returns the population covariance of <numeric field a> and <numeric field b>. The (M) form returns the population covariance of two fields aggregations, grouped by another field.

Syntax (Basic form)

COVARP(<numeric field a>, <numeric field b>)

Arguments

Argument Description

<numeric field a>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.

<numeric field b>

Example:

COVARP([Revenue], [Cost])

Returns the population covariance of revenue and cost.

Syntax (Multipass form)

COVARP(<group-by field>, <aggregation a>, <aggregation b>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which you want to group
<aggregation a> Aggregation function (such as an average, sum, or minimum) of a numeric field
<aggregation b> The same aggregation function on another numeric field

Example:

COVARP([Products], AVG([Revenue]), AVG([Cost]))

Returns the population covariance of the average revenue and the average cost per product.

The basic form returns the sample covariance of <numeric field a> and <numeric field b>. The (M) form returns the sample covariance of two fields aggregations, grouped by another field.

Syntax (Basic form)

COVAR(<numeric field a>, <numeric field b>)

Arguments

Argument Description
<numeric field a> Any database column containing numeric values
<numeric field b> Any database column containing numeric values

Example:

COVAR([Revenue], [Cost])

Returns the sample covariance of revenue and cost.

Syntax (Multipass form)

COVAR(<group-by field>, <aggregation a>, <aggregation b>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which you want to group
<aggregation a> Aggregation function (such as an average, sum, or minimum) of a numeric field
<aggregation b> The same aggregation function on another numeric field

Example:

COVAR([Products], AVG([Revenue]), AVG([Cost]))

Returns the sample covariance of the average revenue and the average cost per product.

Returns the skewness of the distribution of a given value in the population.

Syntax

SKEWP(<numeric field>)

Arguments

Argument Description

<numeric field>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.

Example:

SKEWP([Revenue])

Returns the skewness of the distribution of Revenue in the population.

Returns the skewness of the distribution of a given value in a sample.

Syntax

SKEW(<numeric field>)

Arguments

Argument Description

<numeric field>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.

Example:

SKEW([Revenue])

Returns the skewness of the distribution of Revenue in the sample.

Returns the slope of a linear regression line through the provided series of x and y values.

Syntax

SLOPE(<numeric field a>, <numeric field b>)

Arguments

Argument Description

<numeric field a>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.
<numeric field b> Any database column containing numeric values

Example:

SLOPE([month.int], [Total Sales])

Returns the slope of the regression line that represents a trend of items sold for each month.

Non-aggregative Functions

Returns the exponential distribution for a given value and a supplied distribution parameter mean.

Syntax

EXPONDIST(<numeric field>, <mean>, <cumulative (true/false)>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<mean> Any number representing the distribution mean

<cumulative>

TRUE = Cumulative distribution function

FALSE = Probability density function

Example:

EXPONDIST(COUNT([Leads]), (Mean([Leads]), ALL([Leads])), False)

Returns the exponential distribution density of the number of leads per country.

Returns the intercept of a linear regression line through the provided series of x and y values.

Syntax

INTERCEPT(<numeric not datetime>, <numeric field>)

Arguments

Argument Description

<numeric not datetime>

Any database column containing numeric values.

Date and Time data types are not supported. Convert these types to custom numeric fields.
<numeric field> Any database column containing numeric values

Example:

INTERCEPT([month.int], [Total Sales])

Returns the intercept of the regression line that represents the trend of items sold for each month.

Returns the Normal distribution for a given value, a supplied distribution mean and standard deviation.

Syntax

NORMDIST(<numeric field>, <mean>, <standard deviation>, <cumulative (true/false)>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<mean> Any number representing the distribution mean
<standard deviation> Any number representing the standard deviation

<cumulative>

TRUE = Cumulative Normal Distribution Function

FALSE = Normal Probability Density Function

Example:

NORMDIST(SUM([Score]), (MEAN([Score]), ALL([Score])), (STDEV([Score]), ALL([Score])), False)

Returns the normal probability density of a given student score.

Returns the Poisson distribution for a given value and a supplied distribution mean.

Syntax

POISSONDIST(<numeric field>, <mean>, <cumulative (true/false)>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<mean> Any number representing the distribution mean

<cumulative>

TRUE = Cumulative distribution function

FALSE = Probability mass function

Example:

POISSONDIST(SUM([Score]), (MEAN([Score]), ALL([Score])), False)

Returns the Poisson probability density of a given number of scores.

Returns Student's T-distribution for a given value and a supplied number of degrees of freedom.

Syntax

TDIST(<numeric field>, <degrees_freedom>, <cumulative (true/false)>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<degrees_freedom> Any value representing the degrees of freedom

<cumulative>

TRUE = Cumulative Distribution Function

FALSE = Probability Density Function

Example:

TDIST(SUM([Score]), (COUNT([Score])-1), TRUE)

Returns the student's T-distribution of a given score, with degrees of freedom = one less than the number of data items.

Mathematical Functions

Mathematical Functions Supported by ElastiCube and Live Models

Aggregative Functions

The basic form calculates the sum of a given database column (aggregation). The (M) form calculates the sum off a given table column grouped by another field.

Syntax (Basic form)

SUM(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

SUM([Cost])

Returns the total Cost across all items in the Cost column.

Syntax (Multipass form)

SUM(<group-by field>, <aggregation>)

Arguments

Argument Description
<group-by field> Any database column containing numeric or textual values by which results can be grouped
<aggregation> Aggregation

Example:

SUM([Product],[Sales])

Returns the total Sales values grouped by Product.

Non-aggregative Functions

Note:

Most of the following functions takes a single argument that is either a numeric constant or a numeric aggregative field function result such as SUM(), AVG() etc. Compositions of mathematical functions also work when they are well defined, such as LN(COSH(<numeric field>)). However, compositions like LN(COS(<numeric field>)) will fail when COS(<numeric field>) is less than or equal to zero.

The dashboard functions all have the same syntactic form:

<function_name>(<numeric field>)

or

<function_name>(<numeric field>, <number>)

<function_name> is typically like ABS, SIN, LOG10 and more as shown in the list of functions below.

The argument, <numeric field> is a numeric value or an aggregative function of a database column such as SUM, AVG, STDEV and so on. You can also pick aggregation functions from the function editor:

The shown functions, Sum ... Standard Deviation are all aggregative.

The <number> argument appears MOD, POWER, QUOTIENT and ROUND.

In the following list, functions labeled (EC) are for ElastiCube only.

Non-aggregative Functions

Returns the absolute value of Returns the absolute value of its argument.

Mathematical Equivalent

f(x) = |x|

Syntax

ABS(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

ABS([Cost])

When the value is 2 or -2 the absolute result is 2.

Returns the angle, in radians, whose cosine is the given numeric expression. Also referred to as arccosine, arcos, cos-1.

Mathematical Equivalent

f(x) = cos-1x

Syntax

ACOS(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

ACOS([Total Revenue])

Returns the angle, in radians, whose cosine is the given total revenue.

ACOS(0.5)=>π/3=60°

To convert radians to degrees, use

Value(degrees) = Value(radians)*180/π

For a detailed example of how to use ACOS to determine the distance between two points using latitude and longitude (for logistical purposes such as delivery service, flights, the distance between customers, etc.), see here.

Returns the angle, in radians, whose sine is the given numeric expression. Also referred to as arcsin, arsin, sine-1.

Mathematical Equivalent

f(x) = sin-1x

Syntax

ASIN(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

ASIN([Total Revenue])

Returns the angle, in radians, whose sine is the given total revenue.

ASIN(0.5)=>π/6=30°

To convert radians to degrees, use

Value(degrees) = Value(radians)*180/π

Returns the angle in radians whose tangent is the given numeric expression. Also referred to as arctan, tan-1.

Mathematical Equivalent

f(x) = tan-1x

Syntax

ATAN(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

ATAN([Total Revenue])

Returns the angle in radians whose tangent is the given total revenue.

ATAN(1.0)=>π/4=45°

To convert radians to degrees, use

Value(degrees) = Value(radians)*180/π

Returns the smallest integer greater or equal to the argument.

Mathematical Equivalent

f(x) = ⌈x⌉

Syntax

CEILING(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

CEILING([Total Cost])

For example, when the cost is 83.2 it is rounded up to 84.

CEILING(2.3)=>3

CEILING(-2.3)=>2

Returns the trigonometric cosine of the given angle (in radians).

Mathematical Equivalent

f(x) = cos x

Syntax

COS(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

COS([Average Angle])

Returns the trigonometric cosine of the average angle.

COS(1.0472)=>0.5

1.0472 radian=π/3 = 60°

To convert degrees to radians, use

Value(Radians) = Value(degrees) * π/180

Returns the cotangent of the argument, where the argument is in radians.

Mathematical Equivalent

f(x) = cot x

Syntax

COT(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

COT([Average Angle])

Returns the trigonometric cotangent of the average angle.

COT(0.7854)=>1.0

0.7854 radian=π/4 is 45°

To convert degrees to radians, use

Value(Radians) = Value(degrees) * π/180

Returns e (base of natural logarithms) raised to the power of the argument.

Mathematical Equivalent

f(x) = ex

Syntax

EXP(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

EXP([Sales])

Returns the exponential value of sales.

EXP(2.0)=>7.389

Returns the largest integer less than or equal to the argument.

Mathematical Equivalent

f(x) = ⌊x⌋

Syntax

FLOOR(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

FLOOR([Revenue])

For example, when the revenue is 88.6 it is rounded down to 88.

FLOOR(2.3)=>2

FLOOR(-2.3)=>-3

Returns the natural (base e) logarithm of the argument.

Mathematical Equivalent

f(x) = ln x = loge x

Syntax

LN(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

LN([Cost])

Returns the natural (base e) logarithm of the cost.

LN(2.0)=>0.693

Returns the base 10 logarithm of the argument.

Mathematical Equivalent

f(x) = log10 x

Syntax

LOG10(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

LOG10([Revenue])

Returns the base-10 logarithm of the revenue.

LOG10(2.0)=>0.301

Returns the remainder after a number is divided by a divisor. In this implementation of MOD, the sign of both arguments is ignored, treating them both as positive numbers.

Mathematical Equivalent

f(x) = x mod n where n is the divisor

Syntax

MOD(<numeric field>, <divisor>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<divisor> Any number you want to divide by

Example:

MOD([Cost], 10)

Returns 5 when the cost is 255, (5 is the remainder after 255 divided by 10).

Returns the results of the given value raised to a supplied power.

Mathematical Equivalent

f(x, a) = xa

Syntax

Power(<numeric field>, <power>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<power> Any number you want to raise by the power of

Examples:

POWER([Revenue], 2)

Returns the value of the revenue raised by the power of 2.

POWER(3, 2.5)=>15.5884

Returns the integer portion of a division.

Mathematical Equivalent

f(x, a) = ⌊x/a⌋

Syntax

QUOTIENT(<numeric value>, <divisor>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<divisor> Any number you want to divide by

Example:

QUOTIENT([Cost], 2)

For example, this returns 2 when the cost is 5, (2 is the integer portion of 5 divided by 2).

Returns number rounded to a specified number of digits.

ROUND(<numeric field>, <num_digits>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<num_digits> The number of digits you want to round to

Examples:

ROUND([Revenue], 2)

Returns the revenue value rounded to two decimal places.

ROUND(7.245, 2) =>7.25

and then

ROUND(ROUND(7.245, 2), 1) => 7.3

but

ROUND(7.245, 1)=>7.2

So take care with ROUND.

Returns the sine of the argument, where the argument is in radians.

Mathematical Equivalent

f(x) = sin x

Syntax

SIN(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

SIN([Average Angle])

Returns the sine of the average angle.

SIN(0.5236)=>0.5

0.5236 radian = π/6 is30°

To convert degrees to radians, use

Value(Radians) = Value(degrees) * π/180

Returns the square root of the given value.

Syntax

SQRT(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values. Accepts only positive values.

Example:

SQRT([Cost])

Returns the square root of cost.

Returns the tangent of the argument, where the argument is in radians.

Mathematical Equivalent

f(x) = tan x

Syntax

TAN(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

TAN([Average Angle])

Returns the trigonometric tangent of the average angle.

TAN(0.7854)=>1.0

0.7854 radian = π/4 is 45°

To convert degrees to radians, use

Value(Radians) = Value(degrees) * π/180

Mathematical Functions Supported by ElastiCube Only

Returns the hyperbolic cosine of the argument.

Mathematical Equivalent

f(x) = cosh x

Syntax

COSH(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

COSH([Total Revenue])

Returns the hyperbolic cosine of the total revenue.

Returns the hyperbolic sine of the argument.

Mathematical Equivalent

f(x) = sinh x

Syntax

SINH(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

SINH([Total Revenue])

Returns the hyperbolic sine of the total revenue.

Returns the hyperbolic tangentof the argument.

Mathematical Equivalent

f(x) = tanh x

Syntax

TANH(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Examples:

TANH([Total Revenue])

Returns the hyperbolic tangeent of the total revenue.

Time and Calendar Functions

Returns the difference between <end time> and <start time> in seconds.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

SDIFF(<end time>, <start time>)

Arguments

Argument Description
<end time> Any column containing dates
<start time> Any column containing dates

Example:

SDIFF([Landing Time], [Leaving Time])

Returns the difference in seconds from the time of landing on the page to the time of leaving the page.

Here is an example of using the SDIFF function to return the number of seconds between a create date and the update date:

Syntax

SDIFF(NOW(), <start time>)

Arguments

Argument Description
NOW() The query execution time (according to the Sisense server).
Used to calculate the difference in seconds between the start time and now.
<start time> Any column containing dates

Example:

SDIFF(NOW(), [Leaving Time])

Returns the difference in seconds between the time of landing on the page and now.

Returns the difference between <end time> and <start time> in minutes.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

MNDIFF(<end time>, <start time>)

Arguments

Argument Description
<end time> Any column containing dates
<start time> Any column containing dates

Example:

MNDIFF([Landing Time], [Payment Completed Time])

Returns the difference in minutes from the time of landing on the page to the time of leaving the page.

Here is an example of using the MNDIFF function to return the number of minutes between a create date and the update date:

Syntax

MNDIFF(NOW(), <start time>)

Arguments

Argument Description
NOW() The query execution time (according to the Sisense server).
Used to calculate the difference in minutes between the start time and now.
<start time> Any column containing dates

Example:

MNDIFF(NOW(), [Payment Completed Time])

Returns the difference in minutes between the time of landing on the page and now.

Returns the difference between <end time> and <start time> in hours. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

HDIFF(<end time>, <start time>)

Arguments

Argument Description
<end time> Any column containing dates
<start time> Any column containing dates

Example:

HDIFF([Attendance time],[Check in time])

Returns the difference in hours between the check-in time to the Emergency Room and time of attendance by the doctor. Returns whole numbers.

Here is an example of using the HDIFF function to return the number of hours between a create date and the update date:

Syntax

HDIFF(NOW(), <start time>)

Arguments

Argument Description
NOW() The query execution time (according to the Sisense server).
Used to calculate the difference in seconds between the start time and now.
<start time> Any column containing dates

Example:

HDIFF(NOW(),[Check in time])

Returns the difference in hours between the check-in time to the Emergency Room and now. Returns whole numbers.

Returns the difference between <end time> and <start time> in days.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

DDiff(<end time>, <start time>)

Arguments

Argument Description
<end time> Any column containing dates
<start time> Any column containing dates

Example:

DDIFF([Discharge Time], [Admission Time])

Returns the difference in days from the time of admission to hospital to the time of patient discharge.

Here is an example of using the DDIFF function to return the number of days between a create date and the update date:

Syntax

DDiff(NOW(), <start time>)

Arguments

Argument Description
NOW() The query execution time (according to the Sisense server).
Used to calculate the difference in seconds between the start time and now.
<start time> Any column containing dates

Example:

DDIFF(NOW(), [Admission Time])

Returns the difference in days between the time of admission to hospital and now.

Returns the difference between <end time> and <start time> in months. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

MDIFF(<end time>, <start time>)

Arguments

Argument Description
<end time> Any column containing dates
<start time> Any column containing dates

Example:

MDIFF([Departure Time], [Arrival Time])

Returns the difference in months from the time a ship departs from its departure port to the time of arrival in its destination port. Returns whole numbers.

Here is an example of using the MDIFF function to return the number of months between a create date and the update date:

Syntax

MDIFF(NOW(), <start time>)

Arguments

Argument Description
NOW() The query execution time (according to the Sisense server).
Used to calculate the difference in seconds between the start time and now.
<start time> Any column containing dates

Example:

MDIFF(NOW(), [Arrival Time])

Returns the difference in months between the time a ship departs from its departure port and now. Returns whole numbers.

Returns the difference between <end time> and <start time> in quarters. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

QDIFF(<end time>, <start time>)

Arguments

Argument Description
<end time> Any column containing dates
<start time> Any column containing dates

Example:

QDIFF([StartSemester], [EndSemester])

Returns the difference in quarters from the first academic semester to the graduation semester. Returns whole numbers.

Syntax

QDIFF(NOW(), <start time>)

Arguments

Argument Description
NOW() The query execution time (according to the Sisense server).
Used to calculate the difference in seconds between the start time and now.
<start time> Any column containing dates

Example:

QDIFF(NOW(), [EndSemester])

Returns the difference in quarters between the first academic semester and now. Returns whole numbers.

Returns the difference between <end time> and <start time> in years. Returns whole numbers.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

YDIFF(<end time>, <start time>)

Arguments

Argument Description
<end time> Any column containing dates
<start time> Any column containing dates

Example:

YDIFF([membership end], [membership start])

Returns the difference in years from the start of the membership to the end of the membership. Returns whole numbers.

Syntax

YDIFF(NOW(), <start time>)

Arguments

Argument Description
NOW() The query execution time (according to the Sisense server).
Used to calculate the difference in seconds between the start time and now.
<start time> Any column containing dates

Example:

YDIFF(NOW(), [transaction date])

Returns the difference in years between the transaction date and now. Returns whole numbers.

Here is an example of the YDiff function being used with NOW():

Returns the difference between this week's data and the data from the previous week.

Use this function when the time resolution used in your widget is day or week. Otherwise, it does not display correct data.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

DIFFPASTWEEK(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

DIFFPASTWEEK([Total Sales])

Returns the difference between this week's sales and previous week's sales, for the displayed time resolution.

For day resolution: (sales in current day - sales in same day one week back)

For week resolution: (sales in current week - sales in previous week)

Returns the difference between this month's data and the data from the previous month.

Use this function when the time resolution used in your widget is 'month'. Otherwise, it does not display correct data.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

DIFFPASTMONTH(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

DIFFPASTMONTH([Total Sales])

Returns the difference between this month's sales and previous month's sales, for the displayed time resolution.

For day resolution: (sales in current day - sales in same day one month back)

Returns the difference between this quarter's data and the data from the previous quarter.

Use this function when the time resolution used in your widget is 'month or 'quarter''. Otherwise, it does not display correct data.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

DIFFPASTQUARTER(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

DIFFPASTQUARTER([Total Sales])

Returns the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution.

For month resolution: (sales in current month - sales in same month one quarter back)

For quarter resolution: (sales in current quarter- sales in previous quarter)

Returns the difference between this year's data and the data from the previous year. All time resolutions in the widget are available for this function (year, quarter, month, week, day).

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

DIFFPASTYEAR(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

DIFFPASTYEAR([Total Sales])

Returns the difference between this year's sales and previous year's sales, for the displayed time resolution.

For month resolution: (sales in current month - sales in same month one year back)

For quarter resolution: (sales in current quarter - sales in the same quarter one year back)

For week resolution: (sales in current week - sales in same week one year back)

Returns the difference between this period's data and the data from the previous period.

Formula: (current value - compared value).

Accepts any time resolution (day, week, etc.).

The active time resolution is determined by the lowest time resolution of the widget elements from the filters and slicers. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

DIFFPASTPERIOD(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

DIFFPASTPERIOD([Total Sales])

Returns the difference between this period's sales and the previous period's sales.

Calculates growth over time. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Formula: (current value - compared value) / compared value.

Accepts any time resolution (day, week, etc.) in the widget.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

GROWTH(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

GROWTH([Total Quantity])

If this month your Total Quantity is 12, and last month it was 10, your Growth for this month is 20% (0.2). Calculation: (12 - 10) / 10 = 0.2

If this year your Total Quantity is 80, and last year it was 100, your Growth for this year is -20% ( -0.2). Calculation: (80 - 100) / 100 = -0.2

Calculates growth over time. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Formula:(current value - compared value) / compared value.

Accepts any time resolution (day, week, etc.).

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

GROWTHRATE(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

GROWTHRATE([Total Quantity])

If this month your Total Quantity is 12, and last month it was 10, your Growth Rate for this month is 12/10 = 120% (1.2). Calculation: 12 / 10 = 1.2

If this year your Total Quantity is 80, and last year it was 100, your Growth for this year is 80/100 = 80% ( 0.8). Calculation: 80 / 100 = 0.8

Calculates the growth from the past week to the current week. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Use this function when the time resolution in your widget is weeks or days. Otherwise, it does not display any data.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

GROWTHPASTWEEK(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

GROWTHPASTWEEK([Total Sales])

Calculates the difference between this week's sales and previous week's sales, for the displayed time resolution.

For day resolution: (sales in current day - sales in same day one week back) / sales in same day one week back

For week resolution: (sales in current week - sales in previous week / sales in previous week)

Calculates the growth from the past month to the current month. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.
Use this function when the time resolution in your widget is month or day. Otherwise, it does not display any data.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

GROWTHPASTMONTH(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

GROWTHPASTMONTH([Total Sales])

Calculates the difference between this month's sales and previous month's sales, for the displayed time resolution.

For day resolution: (sales in current day - sales in same day one month back) / sales in same day one month back

Calculates the growth from the past quarter to the current quarter. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Use this function when the time resolution in your widget is month or quarter. Otherwise, it does not display any data.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

GROWTHPASTQUARTER(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

GROWTHPASTQUARTER([Total Sales])

Calculates the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution.

For month resolution: (sales in current month - sales in same month one quarter back) / sales in same month one quarter back

For quarter resolution: (sales in current quarter - sales in previous quarter) / sales in previous quarter

Calculates the growth from the past year to the current year. Growth compares the results of two calculations, each based on a different time period, and the result is the union of all results, where at least one valid result is returned. ±100 will indicates that only one valid result was returned.

Use this function when the time resolution in your widget is week, month, quarter, year.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

GROWTHPASTYEAR(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values

Example:

GROWTHPASTYEAR([Total Sales])

Calculates the difference between this year's sales and previous year's sales, for the displayed time resolution.

For week resolution: (sales in current week - sales in same week one year back / sales in same week one year back)

For month resolution: (sales in current month - sales in same month one year back / sales in same month one year back)

Returns the value for the previous day. Accepts the time resolution day.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

PASTDAY(<numeric field>, <Numeric of periods>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<number of periods> Optional. The number of prior periods to use for the calculation

Example:

PASTDAY([Total Sales], 2)

If you are looking at a specific day, you will see the value for 2 days prior to the day specified.

Returns the value for the same period in the previous week. Accepts the time resolutions day, week.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

ASTWEEK(<numeric field>, <number of periods>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<number of periods> Optional. The number of prior periods to use for the calculation

Example:

PASTWEEK([Total Sales], 2)

Returns the Total Sales value two weeks back for the displayed time resolution.

If you are looking at a specific day, you will see the value of the same day two weeks back.

Returns the value for the same period in the previous month. Accepts the time resolutions day, month.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

PASTMONTH(<numeric field>, <number of periods>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<number of periods> Optional. The number of prior periods to use for the calculation

Example:

PASTMONTH([Total Sales], 2)

Returns the Total Sales value two months back for the displayed time resolution.

If you are looking at a specific day, you will see the value of the same day two months back.

Returns the value for the same period in the previous quarter. Accepts the time resolutions day, month, quarter.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

PASTQUARTER(<numeric field>, <number of periods>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<number of periods> Optional. The number of prior periods to use for the calculation

Example:

PASTQUARTER([Total Sales], 2)

Returns the Total Sales value two quarters back for the displayed time resolution.

If you are looking at a specific day, you will see the value of the same day two quarters back. If you are looking at a specific month, you will see the value of the same month two quarters back.

Returns the value for the same period in the previous year. Accepts any time resolution (day, week, etc.).

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null. The formula calculation succeeds if both the presentation scope calculation and past date scope calculation return values. Otherwise, it returns null.

Syntax

PASTYEAR(<numeric field>, <number of periods>)

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<number of periods> Optional. The number of prior periods to use for the calculation

Example:

PASTYEAR([Total Sales], 2)

Returns the Total Sales value two years back for the displayed time resolution.

If you are looking at a specific day, you will see the value of the same day two years back. If you are looking at a specific month, you will see the value of the same month two years back.

Returns the running average starting from the beginning of the week up to the current day. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is years, quarters, or months.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

WTDAVG(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

WTDAVG([Total Sales])

Returns the running average of Total Sales starting from the beginning of the week up to the desired day.

Returns the running total starting from the beginning of the week up to the current day or week. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is years, quarters, or months.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

WTDSUM(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

WTDSUM([Total Sales])

Returns the running total of Total Sales starting from the beginning of the week up to the current day.

Returns the running average starting from the beginning of the month up to the current day. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Use this function when the active time resolution in your widget is 'days'. Returns null if the active time resolution is quarters or years or weeks.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

MTDAVG(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

MTDAVG([Total Quantity])

Returns the running Total Quantity average starting from the beginning of the month up to the current day.

Returns the running total starting from the beginning of the month up to the current day. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Use this function when the active time resolution in your widget is 'days'. Returns null if the active time resolution is quarters or years or weeks.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

MTDSUM(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

MTDSUM([Total Quantity])

Returns the running total of Total Sales starting from the beginning of the month up to the current day.

Returns the running average starting from the beginning of the quarter up to the current day or month. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is weeks.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

QTDAVG(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

QTDAVG([Total Sales])

Returns the running average of Total Sales starting from the beginning of the quarter up to the desired day or month.

Returns the running total starting from the beginning of the quarter up to the current day or month. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the active time resolution is weeks.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

QTDSUM(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

QTDSUM([Total Sales])

Returns the running total of Total Sales starting from the beginning of the quarter up to the current day or month.

Returns the running average starting from the beginning of the year up to the current day, week, month, quarter or year. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the query is invalid or returns no result.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

YTDAVG(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

YTDAVG([Total Sales])

Returns the running average of Total Sales starting from the beginning of the week up to the desired day, week, month, quarter or year.

Returns the running total starting from the beginning of the year up to the current day, week, month, quarter or year. The formula gets the active date scope from the filters and presentation scope, and can calculate over future dates.

Returns null if the query is invalid or returns no result.

The active time resolution is determined by the minimum date level of the date dimension used for presentation and filtering. If there is no active time resolution, the formula returns null.

Syntax

YTDSUM(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

YTDSUM([Total Sales])

Returns the running total of Total Sales starting from the beginning of the year up to the current day, week, month, quarter or year.

Measured Value Functions

These functions only work as part of a measured value, not by themselves. See Measured Values for more information.

Note:

The information below for the "All" function is the minimal information needed for those looking for a quick reference. See Measured Values for more complete information.

Changes the scope of a measure calculation to ignore a dimension being used in the widget for presentation or as a filter.

This function can only work as part of a measured value, not by itself.

Note:

The All function is a way to ignore a filter while calculating the results of the formula. It does not eliminate the filter applied on the dimension itself.

Syntax

ALL(<any data type>)

Arguments

Argument Description
<any data type> Any groupable database column. (Not restricted to dates.)

Example:

(SUM([Items]),ALL([Years in Date]))

When used for a widget that has a filter to show last year's data, this formula will ignore the filter in the calculation.

Here is an example of a widget using the All function to ignore the grouping by Date filtering:

Here is an example of a widget using the All function to ignore grouping according to the values in the Age Range, (a text column):

Returns the Time period Member in <time field> which is N periods back from the current member.

This function can only work as part of a measured value, not by itself.

This function works will all time resolutions. However, en sure that the active time resolution in the widget matches the time resolution in the function.

Example:

(SUM([Quantity]),PREV([Months in Date]))

For this function the active time resolution must be "months".

Syntax

((<numeric field>), PREV(<time field>, <N>))

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<time field> Any database column containing dates
<N> Optional. The number of time periods to go back.

Example:PREV

(SUM[Quantity],PREV([Months in Date],2))

This formula returns the Total Quantity value for the month that occurred two months ago.

In the following example, the first column is the date (grouped by year), the next 2 columns are different ways to get the same total revenue for each year, column 4 uses Prev to return the values from the previous year, and column 5 uses Prev with the optional field to return the values from 2 years prior:

Here, PREV overrides the default yearly group-by filtering for the Revenue, and causes the Revenue to be filtered according to the previous year, (or earlier, based on the optional field).

Returns the value for the time-period member in <time field> which is N periods after the current member.

This function can only work as part of a measured value, not by itself.

This function works will all time resolutions. However, make sure that the active time resolution in the widget matches the time resolution in the function.

Example:

(SUM([Quantity]),NEXT([Weeks in Date]))

For this function the active time resolution must be "weeks".

Syntax

((<numeric field>), NEXT(<time field>, <N>))

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<time field> Any database column containing dates
<N> Optional. The number of time periods to go forward

Example:

(SUM([Quantity]),NEXT([Months in Date],2))

This formula returns the total quantity value for the month that is two months ahead.

In the following example, the first column is the date (grouped by year), the next 2 columns are different ways to get the same total revenue for each year. Column 4 uses Next to return the values from the following year, and column 5 uses Next with the optional field to return the values for 2 years later:

Here, NEXT overrides the default yearly group-by filtering for the Revenue, and causes the Revenue to be filtered according to the following year, (or later, based on the optional field).

Returns the value for the current time period. The NOW function receives a date dimension and its level and returns all the members in that dimension which match the current query execution time.

This function can only work as part of a measured value, not by itself.

Use this function when the time resolution in your widget is day, month, quarter, year.

Syntax

((<numeric field>), NOW(<time field>))

Arguments

Argument Description
<numeric field> Any database column containing numeric values
<time field> Any database column containing dates

Example:

(SUM([Quantity]),NOW([Months in Date]))

This formula returns the total quantity value for the current month.

Here is an example of a widget using the Now function to only return the values for the current year, (2023):

Logical and Conditional Functions

Other Functions Supported by ElastiCube and Live Models

These functions are supported for both ElastiCube and Live models.

Returns the <result expression> of the first <condition> that evaluates as true. If no condition is true, the <result expression> for the ELSE is returned, (if defined).

Syntax

(WHEN <condition> THEN <result expression> [...] [ELSE <result expression>] END)

Arguments

Argument Description
<condition> Any formula or a function that is evaluated.
<result expression> Any number, formula or a function that is returned if the relevant condition is true.

Example:

{put in code block}CASE WHEN SUM([Sales]) < 100 THEN 1 WHEN SUM([Sales]) < 1000 THEN 2 ELSE 3 END

Returns '1' when the Total Sales value is between 100 and 1000. Returns '2' if the Total Sales value is above 1000. Returns '3' in any other case (meaning, when Total Sales are below 100).

Returns <numeric expression 1> when the <condition> is true, and it returns <numeric expression 2> when the <condition> is false. Nested conditional statements are supported.

Syntax

IF(<condition>, <numeric expression 1>, <numeric expression 2>)

Arguments

Argument Description
<condition> Boolean expression
<numeric expression 1> Returned when <condition> is true
<numeric expression 2> Returned when <condition> is false

Example:

IF(COUNT([Sales])>100, SUM([Sales])*1.1, SUM([Sales]))

If the number of unique values within the Sales values is larger than 100, the function will return the Total Sales x 1.1 (sales increase of 10%). Otherwise - if the number of unique values within the Sales values is lower than 100, will return only the Total Sales, without an increase.

Here is an example of a widget that uses the If function. In column 3 it indicates that the number of sales (revenue records) for Brand ID 1 is less than 4 by displaying the numeric value "0". In column 4 it indicates that the number of sales*2 (which equals 6) is greater than 4 by displaying the numeric value "999":

Note:

Row level operations are only supported on aggregations, with the exception of arithmetic operators* on fields inside aggregations. For example, Sin(Sum(cost)) is valid, but Sum(Sin(cost)) is not valid. The products of these operations are called calculated facts because they are calculated in query time, but they are only allowed on dimensions that are known to be used for aggregation. The Calculated Dimension feature, which enables performing operations over dimensions that are used in pivots, such as subString(“age range”, 1, 2), is not supported.

*Arithmetic Calculated Fact - Applying operations on fields on formulas is supported only for arithmetic operations, such as “+”, “-”,”/”,”*”.

Therefore, please note the following limitations and potential workaround for the IF formula.

Limitations:

  • The IF formula only works when the parameters are aggregated. For example, Sin(Sum(cost)) is valid, but Sum(Sin(cost)) is not valid.

  • Calculated Fact is only partially supported.

Workaround:

Add a custom column with the IF formula with the non-aggregated parameters.

Returns true if the expression does not contain data (Null), where the dimensions used in the query for presentation return any data.

Syntax

ISNULL(<numeric field>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

Can be used as a condition when writing conditional statements.

When the NOW function is used without any arguments it returns the query execution time (according to the Sisense server).

The NOW function can also effectively act as the Date parameter in the DATEDIFF functions (Day Difference, Hour Difference, Minutes Difference, Month Difference, Quarter Difference, Second Difference, Year Difference). See how to use these functions in the Dashboard Functions Reference section above.

Syntax

NOW()

For example, here is the NOW() function being used by the YDIFFfunction:

Returns the running total of the measure by the defined dimension according to the current sorting order in the widget.

By default, RSUM accumulates a measure by the sorting order of the dimension. To accumulate by another order, the relevant measure should be added as an additional column and sorted.

Syntax

RSUM (<numeric field>),

RSUM (<numeric field>, <continuous>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data
<continuous> A Boolean value that accumulates the sum continuously when there are two or more dimensions. The default value is FALSE.

Limitations

Usually, when sorting by value (measure) where there are multiple slicers, the widget is sorted by both value and all slicers other than the last slicer.

When adding filters such that only one row remains for each slicer, the table is sorted only by value and is not sorted by slicer:

RSUM is highly dependent on the sorting. Therefore, when there is only one row per slicer, and the widget’s sorting is lost, the continuous RSUM column and other values columns do not match.

  • Running Sum is not supported with Measured Values. This means that filters will have to be applied on the widget.

Note:

Filtering the RSUM column by Values will filter the dimensions and recalculate the RSUM from the first filtered value.

Example:

RSUM([Total Revenue], FALSE)

Returns the running total of the Total Revenue measure.

Other Functions Supported by ElastiCube Only

These functions are only supported for ElastiCubes.

Returns the numeric order position of rows sorted into ascending or descending order, breaking ties with further arguments.

The expressions must be aggregated by applying the MIN/MAX functions.

Syntax

ORDERING(<expression1>, <expression2>)

Arguments

Argument Description
<numeric field> Any database column containing numeric data

Example:

ORDERING(MIN([Sales Person Name]), MIN([Days in Transaction_Date]), -1*SUM([Sales]))

Returns a numeric result for a given R expression and a list of numeric values.

The R expression is passed to the running Rserve.

Syntax

{use code block} RDOUBLE(<R expression>, [<ordering>], <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

RDOUBLE(<recycle>, <R expression>, [<ordering>], <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

Argument

Argument Description

<R expression>

Your R code, wrapped in double quotes. R expects the return type to be an array with the same size as widget's row count. Nulls will be used to make up for shorter arrays, and longer arrays will be trimmed.

Use single quotes to wrap strings within your R code, so that there will be no double-quote collision with the quotes wrapping your R code.

<Numeric Field>

Numeric values can be passed as arguments to your R code. All arguments are passed to R as a 1-based list named "args". Each item in the list contains an array that represents the field.

Example:

  • args[[1]] will return an array which represents the first field that was used as an argument.
  • args[[2]][3] will return the 3rd data value within the 2nd field that was used as an argument.

<Ordering>

Optional. Defines the sort order in which numeric data is sent to R. The argument of the Ordering parameter can be an index in your data source or you can use the ORDERING() function to determine the order of your fields. This function arranges the values of the arguments into ascending or descending order, breaking ties by further arguments.

Example:

'ORDERING([Total Sales], -1*[COUNT Salesman], MIN(<Office Name>))'

For more information about the ORDERING() function, click Ordering (EC).

<recycle>

Optional. Controls whether the results from R should be recycled (cached), so that consequent queries will not have to be recalculated unless they or the data have changed.

Generally, this behavior is automatically managed by the ElastiCube automatically. However, since R code might have non-deterministic components to it (such as randomality functions or date-specific functions), the ElastiCube cannot rely on a data-set and function that has not changed not to return a different result in multiple executions.

TRUE (default) - Results will be cached for unchanged functions and data.

FALSE - Results will not be cached. Use this option if your R code contains randomality or other non-deterministic content.

Example:

RDOUBLE("m <- log(matrix(unlist(args), ncol=2)); kmeans(m,3)$cluster", [Total Cost], [Total Revenue])

Returns the k-means cluster (R expression) of the args: [Total Cost] and [Total Revenue].

For additional discussion on using RDouble and how to do advanced forecasting with R, see here.

Returns an integer result for a given R expression and a list of numeric values.

The R expression is passed to the running Rserve.

Syntax

{use code block} RINT(<R expression>, [<Ordering>], <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

RINT(<recycle>, [<Ordering>], <R expression>, <numeric value 1>, [<numeric value 2>, ..., <numeric value n>])

Arguments

Argument Description

<R expression>

Your R code, wrapped in double quotes. R expects the return type to be an array with the same size as widget's row count. Nulls will be used to make up for shorter arrays, and longer arrays will be trimmed.

Use single quotes to wrap strings within your R code, so that there will be no double-quote collision with the quotes wrapping your R code.

<Numeric Field>

Numeric values can be passed as arguments to your R code. All arguments are passed to R as a 1-based list named "args". Each item in the list contains an array that represents the field.

Example:

  • args[[1]] will return an array which represents the first field that was used as an argument.
  • args[[2]][3] will return the 3rd data value within the 2nd field that was used as an argument.

<Ordering>

Optional. Defines the sort order in which numeric data is sent to R. The argument of the Ordering parameter can be an index in your data source or you can use the ORDERING() function to determine the order of your fields. This function arranges the values of the arguments into ascending or descending order, breaking ties by further arguments.

Example:

'ORDERING([Total Sales], -1*[COUNT Salesman], MIN(<Office Name>))'

For more information about the ORDERING() function, click Ordering (EC).

<recycle>

Optional. Controls whether the results from R should be recycled (cached), so that consequent queries will not have to be recalculated unless they or the data have changed.

Generally, this behavior is automatically managed by the ElastiCube automatically. However, since R code might have non-deterministic components to it (such as randomality functions or date-specific functions), the ElastiCube cannot rely on a data-set and function that has not changed not to return a different result in multiple executions.

TRUE (default) - Results will be cached for unchanged functions and data.

FALSE - Results will not be cached. Use this option if your R code contains randomality or other non-deterministic content.

Example:

RINT("m <- log(matrix(unlist(args), ncol=2)); kmeans(m,3)$cluster", [Total Cost], [Total Revenue])

Returns the k-means cluster (R expression) of the args: [Total Cost] and [Total Revenue]

For additional discussion on using RInt and how R works with Sisense, see here.