This page contains a list of all the functions you can use in Sisense’s formula editor.

Statistical Functions

Average

Avg(<numeric Field>)

Calculates the mean average of the given values.
For example – AVG(Score) will calculate the mean average of the given scores.

Avg(<group by field>, <aggregation>)

Calculates the average of the given aggregation grouped by another filed.
For example – Avg( Product, Total Sales) will calculates the average of the total sales per product.

Contribution

Contribution(<numeric field>)

Calculates the percentage of total.
For example – Contribution( Total Sales ) will calculate the percentage of total sales per group (for example per day or per product) out of total sales (for all days or all products).

Correlation

CORREL(<Numeric Field a>, <Numeric Field b>)

Returns the correlation coefficient of two numeric fields.
For example – CORREL(Revenue, Cost) will return the correlation between revenue and cost.

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

Returns the correlation coefficient of two fields aggregations grouped by another field.
For example – CORREL(Products, AVG(Revenue), AVG(Cost)) will return the correlation between the average of revenue and cost per product.

Count

Count(<Numeric Field>)

Counts the number of unique values withing the given values.

Count All

DupCount(<Numeric Field>)

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

Covariance (Population)

COVARP(<Numeric Field a>, <Numeric Field b>)

Returns the population covariance of <Numeric Field a> and <Numeric Field b>.
For example – COVARP(Revenue, Cost) will return the population covariance of revenue and cost.

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

Returns the population covariance of two fields aggregations grouped by another field.
For example – COVARP(Products, AVG(Revenue), AVG(Cost)) will return the population covariance of the average revenue and the average cost per product.

Covariance (Sample)

COVAR(<Numeric Field a>, <Numeric Field b>)

Returns the sample covariance of <Numeric Field a> and <Numeric Field b>.
For example – COVAR(Revenue, Cost) will return the sample covariance of revenue and cost.

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

Returns the sample covariance of two fields aggregations grouped by another field.
For example – COVAR(Products, AVG(Revenue), AVG(Cost)) will return the sample covariance of the average revenue and the average cost per product.

Exponential Distribution

EXPONDIST(<numeric value>, <lambda>, <Cumulative (true/false)>)

Returns the exponential distribution for a given value and a supplied distribution parameter lambda. Cumulative: TRUE = Cumulative distribution function, FALSE = Probability density function. For example – EXPONDIST( Count(Leads), 2, False ) will return  the exponential distribution density of the number of leads per country where lambda is 2.

Intercept

INTERCEPT(<field>, <numeric value>)

Returns the intercept of the linear regression line through a supplied series of x- and y- values.
For example – INTERCEPT(Date.Quarter, Total Sales) will return the intercept of the regression line that represents the trend over quarter of the sum of sales.

Largest

LARGEST(<Numeric Field>, <k>)

Returns the k-th largest value in a field.

Maximum

Max(<Numeric Field>)

Returns the maximum value among the given values.

Median

MEDIAN( <Numeric Field> )

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.

Minimum

Min(<Numeric Field>)

Returns the minimum value among the given values.

Mode

MODE(<Numeric Field>)

Returns the most frequently occurring value from the column.

Normal Distribution

NORMDIST(<Numeric Field>, <Mean>, <Standard Deviation>, <Cumulative (true/false)>)

Returns the standard normal distribution for a given value, a supplied distribution mean and standard deviation. Cumulative: TRUE = Cumulative Normal Distribution Function, FALSE = Normal Probability Density Function.
For example – NORMDIST(Score, ( Mean(Score), All(Score)), ( STDEV(Score), All(Score) ), False ) will return the normal probability density of a given score.

Percentile

PERCENTILE(<Numeric Field>, <k>)

Returns the k-th percentile value from the given field.
k is any number between 0..1 (inclusive).

Possion Distribution

POISSONDIST( <numeric value>, <mean>, <Cumulative (true/false)>)

Returns the poisson distribution for a given value and a supplied distribution mean. Cumulative: TRUE = Cumulative distribution function, FALSE = Probability mass function.
For example – POISSONDIST( Score, ( Mean(Score), All(Score) ), ( STDEV(Score), All(Score) ), False ) will return the poisson probability density of a given number of sales

Quartile

QUARTILE(<Numeric Field>, <k>)

Returns the k-th quartile for the given field.

  • 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

Rank

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

Returns the rank of a value in a list of values.
[DESC/ASC] – Optional. By default sort order is descending.
[Rank Type] – Optional. By default the type is standard competition ranking (“1224” ranking). Support also modified competition ranking (“1334” ranking), dense ranking (“1223” ranking) and ordinal ranking (“1234” ranking).
[<Group by field 1>,… , <Group by field n>] – Optional. Rank partitions fields.

For example – RANK(Total Cost, “ASC”, “1224”, Product,  Years) will return the rank of the total annual cost per each product were sorted in ascending order.

Skewness (Population)

SKEWP(<numeric value>)

Returns the skewness of the distribution of a given value in the population.
For example – SKEWP(Revenue) will return the skewness of the distribution of revenue in the population.

Skewness (Sample)

SKEW(<numeric value>)

Returns the skewness of the distribution of a given value.
For example – SKEW(Revenue) will return the skewness of the distribution of revenue.

Slope

SLOPE(<field>, <numeric value>)

Returns the slope of the linear regression line through a supplied series of x- and y- values.
For example – SLOPE(Date.Quarter, Total Sales) will return the slope of the regression line that represent the trend over quarter of the sum of sales.

Standard Deviation (Population)

STDEVP( <Numeric Value> )

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 of survey respondents.

Standard Deviation (Sample)

STDEV( <Numeric Value> )

Returns the Standard Deviation of the given values (Sample).  Standard deviation is the square root of the 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.

T Distribution

TDIST( <numeric value x>,<degrees_freedom>, <Cumulative (true/false)>)

Returns the student’s T-distribution for a given value and a supplied number of degrees of freedom (must be ≥ 1). Cumulative: TRUE = Cumulative Distribution Function, FALSE = Probability Density Function.
For example – TDIST( Score, 3, TRUE ) will return the student’s T-distribution of a given score, with 3 degrees of freedom.

Variance (Population)

VARP( <Numeric Value> )

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

Variance (Sample)

VAR( <Numeric Value> )

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

Mathematical Functions

Absolute

Abs(<Numeric value>)

Returns the absolute value of the given value.
For example – ABS(Cost), where the absolute result for the value ‘2’ or ‘-2’ is ‘2’.

Acos

ACOS(<numeric value>)

Returns the angle, in radians, whose cosine is the given numeric expression. Also referred to as arccosine.
For example – ACOS(Total Revenue) will return the angle, in radians, whose cosine is the given total revenue.

Asin

ASIN(<numeric value>)

Returns the angle, in radians, whose sine is the given numeric expression. Also referred to as arcsine.
For example – ASIN(Total Revenue) will return the angle, in radians, whose sine is the given total revenue.

Atan

ATAN(<numeric value>)

Returns the angle in radians whose tangent is the given numeric expression. Also referred to as arctangent.
For example – ATAN(Total Revenue) will return the angle in radians whose tangent is the given total revenue.

Ceiling

CEILING(<numeric value>)

Returns number rounded up, away from zero, to the nearest multiple of significance.
For example – CEILING(Cost), where the result of ‘83.2’ rounded up is ’84’.

Cos

COS(<numeric value>)

Returns the trigonometric cosine of the given angle (in radians).
For example – COS(Average Angle) will return the trigonometric cosine of the average angle.

Cosh

COSH(<numeric value>)

Returns the hyperbolic cosine of the given value.
For example – COSH(Total Revenue) will return the hyperbolic cosine of the total revenue.

Cot

COT(<numeric value>)

Returns the trigonometric cotangent of the given angle (in radians).
For example – COT(Average Angle) will return the trigonometric cotangent of the average angle.

Exp

EXP(<numeric value>)

Returns the exponential value of the given value.
For example – EXP(Sales) will return the exponential value of sales.

Floor

FLOOR(<numeric value>)

Returns number rounded down, toward zero, to the nearest multiple of ‘1’.
For example – FLOOR(Revenue), where the result of ‘88.6’ rounded down is ’88’.

Ln

LN(<numeric value>)

Returns the base-e logarithm of the given value.
For example – LN(Cost) will return the base-e logarithm of cost.

Log10

LOG10(<numeric value>)

Returns the base-10 logarithm of the given value.
For example – LOG10(Revenue) will return the base-10 logarithm of revenue.

Mod

MOD(<numeric value>, divisor)

Returns the remainder after a number is divided by a divisor.
For example – MOD(Cost, 10), where the reminder of ‘255’ divided by ’10’ is ‘5’.

Power

Power(value, power)

Returns the results of the given value raised to a supplied power.
For example – POWER(Revenue, 2) will return revenue raised by the power of 2.

Quotient

QUOTIENT(<numeric value>, divisor)

Returns the integer portion of a division.
For example – QUOTIENT(Cost, 2), where the integer portion of ‘5’ divided by ‘2’ is ‘2’.

Round

ROUND(<numeric value>, num_digits)

Returns number rounded to a specified number of digits.
For example – ROUND(Revenue, 2) will return the revenue rounded to two decimal places.

Sin

SIN(<numeric value>)

Returns the trigonometric sine of the given angle (in radians).
For example – SIN(Average Angle) will return the trigonometric sine of the average angle.

Sinh

SINH(<numeric value>)

Returns the hyperbolic sine of the given value.
For example – SINH(Total Revenue) will return the hyperbolic sine of the total revenue.

Square root

SQRT(<Numeric value>)

Returns the square root of the given value.
For example – SQRT(Cost) will return the square root of cost.

Sum

Sum(<Numeric Field>)

Calculates the total of the given values.

Tan

TAN(<numeric value>)

Returns the trigonometric tangent of the given angle (in radians).
For example – TAN(Average Angle) will return the trigonometric tangent of the average angle.

Tanh

TANH(<numeric value>)

Returns the hyperbolic tangent of the given value.
For example – TANH(Total Revenue) will return the hyperbolic tangent of the total revenue.

Time Related Functions

Day Difference

DDiff( <Start Time>, <End Time> )

Returns the difference between <Start Time> and <End Time> in days.

Growth

Growth( <Numeric Value> )

Calculates growth over time. The time dimension to be used is determined by the time resolution in the widget/dashboard.
Formula: (current value – compared value) / compared value.
For example:

  • If this month your value 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 value is 80, and last year it was 100, your Growth for this year is -20% ( -0.2).
    Calculation: (80 – 100) / 100 = -0.2

Growth Rate

GrowthRate( <Numeric Value> )

Calculates growth rate over time. The time dimension to be used is determined by the time resolution in the widget/dashboard.
For example:

  • If this month your value 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 value is 80, and last year it was 100, your Growth for this year is 80/100 = 80% ( 0.8).
    Calculation: 80 / 100 = 0.8

Hour Difference

HDiff( <Start Time>, <End Time> )

Returns the difference between <Start Time> and <End Time> in hours.

Prev

Prev( <Time Field> [, <N>] )

Returns the Time period Member in <Time Field> which is N periods back from the current Member. This function only works as a scope function and not by itself.
For example – This formula will return the numeric value 2 months ago:

(<Numeric Value>, Prev(<Month Field>, 2))

Minute Difference

MnDiff( <Start Time>, <End Time> )

Returns the difference between <Start Time> and <End Time> in minutes.

Month Difference

MDiff( <Start Time>, <End Time> )

Returns the difference between <Start Time> and <End Time> in months. Returns whole numbers.

Month to Date Average

MTDAvg( <Numeric Value> )

Returns the running average starting from the beginning of the month up to the current time period member.
The time dimension to be used is determined by the time resolution in the widget/dashboard.
Returns 0 if the active time resolution is quarters or years.

Month to Date Sum

MTDSum( <Numeric Value> )

Returns the running total starting from the beginning of the month up to the current time period member.
The time dimension to be used is determined by the time resolution in the widget/dashboard.
Returns 0 if the active time resolution is quarters or years.

Next

Next( <Time Field> [, <N>] )

Returns the Time period Member in <Time Field> which is N periods after the current Member. This function only works as a scope function and not by itself.
For example – This formula will return the numeric value 2 months ahead of now:

(<Numeric Value>, Next(<Month Field>, 2)

Now

Now(<Day from Date field>)

Returns the value for the current time period. Supports day, month, quarter or year. 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.

Note: This function only works as a scope function and not by itself.

The following example will return the value for the current day.

([Total Sales], Now([Days in Datefield]))

Past Year

PastYear( <Numeric Value> )

Calculates the value for the same period in the past (previous) year.
For example:

  • If you’re looking at a specific day, you will see the value of the same day one year back.
  • If you’re looking at a specific month, you will see the value of the same month one year back.

Past Quarter

PastQuarter( <Numeric Value> )

Calculates the value for the same period in the past (previous) quarter.
For example:

  • If you’re looking at a specific day, you will see the value of the same day one quarter back.
  • If you’re looking at a specific month, you will see the value of the same month one quarter back.

Past Month

PastMonth( <Numeric Value> )

Calculates the value for the same period in the past (previous) month.
For example:

  • If you’re looking at a specific day, you will see the value of the same day one month back.

Quarter Difference

QDiff( <Start Time>, <End Time> )

Returns the difference between <Start Time> and <End Time> in quarters. Returns whole numbers.

Quarter to Date Average

QTDAvg( <Numeric Value> )

Returns the running average starting from the beginning of the quarter up to the current time period member.
The time dimension to be used is determined by the time resolution in the widget/dashboard.
Returns 0 if the active time resolution is years.

Quarter to Date Sum

QTDSum( <Numeric Value> )

Returns the running total starting from the beginning of the quarter up to the current time period member.
The time dimension to be used is determined by the time resolution in the widget/dashboard.
Returns 0 if the active time resolution is years.

Range

range( <Field1>, <Field2> )

Returns a graphical range selector for a data set where two members of the same dimension and level define the minimum and maximum values of the range.

Second Difference

SDiff( <Start Time>, <End Time> )

Returns the difference between <Start Time> and <End Time> in seconds.

Year Difference

YDiff( <Start Time>, <End Time> )

Returns the difference between <Start Time> and <End Time> in years. Returns whole numbers.

Year to Date Average

YTDAvg( <Numeric Value> )

Returns the running average starting from the beginning of the year up to the current time period member.
The time dimension to be used is determined by the time resolution in the widget/dashboard.

Year to Date Sum

YTDSum( <Numeric Value> )

Returns the running total starting from the beginning of the year up to the current time period member.
The time dimension to be used is determined by the time resolution in the widget/dashboard.

Other

All

All(<Field>)

Ignores the scope set on the dimension.

Ordering

ORDERING(<expression1>,<expression2>)

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 as in the example below:

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

Rdouble

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

Returns a numeric result for a given R expression and a list of numeric values (use ‘args[[i]]’ in the R expression to reference numeric values parameters).
The R expression is passed to the running Rserve.

The optional ordering expression determines the order in which the rows are 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 the field. For more information about the ORDERING() function, click here.

For example – RDOUBLE(“m <- log(matrix(unlist(args), ncol=2)); kmeans(m,3)$cluster”, [Total Cost], [Total Revenue]) will return the k-means cluster (R expression) of the args: [Total Cost] and [Total Revenue].

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

recycle = TRUE (default) – Results will be cached for unchanged functions and data.
recycle = FALSE – Results will not be cached. Use this option if your R code contains randomality.

Rint

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

Returns an integer result for a given R expression and a list of numeric values (use ‘args[[i]]’ in the R expression to reference numeric values parameters).
The R expression is passed to the running Rserve.

The optional ordering expression determines the order in which the rows are 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 the field. For more information about the ORDERING() function, click here.

For example – RINT(“m <- log(matrix(unlist(args), ncol=2)); kmeans(m,3)$cluster”, [Total Cost], [Total Revenue]) will return the k-means cluster (R expression) of the args: [Total Cost] and [Total Revenue].

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

recycle = TRUE (default) – Results will be cached for unchanged functions and data.
recycle = FALSE – Results will not be cached. Use this option if your R code contains randomality.