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.

#### Running Sum (RSUM)

RSUM ( <numeric value> ), RSUM ( <numeric value> , <continuous> )

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.

<continuous> is a boolean value that that accumulates the sum continuously when there are two or more dimensions. The default value is False.

Note: Filtering the RSUM column by Values, filters the dimensions and recalculates the RSUM from the first filtered value.

#### 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.