Dashboard Function Reference
  • 17 Nov 2022
  • 46 Minutes to read
  • Dark
    Light

Dashboard Function Reference

  • Dark
    Light

The following is a list of all the functions you can use in Sisense’s formula editor.

  • Aggregative functions are marked with (A) next to their names.
  • Row functions are marked with (R) next to their names.
  • Multi-pass functions are marked with (M) next to their names. Multi-pass Declaration - ([Dimension], Aggregation)) is a group by statement, used with aggregate functions to group the result set by one column and return a list. This function must be part of another aggregative function.
    Functions that are only supported for ElastiCubes are marked with (EC) next to their names. All other functions are supported for both ElastiCube and Live models. For example, AVG([Brand], SUM(Cost)) will calculate the average of total cost per brand.
Note

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

Statistical Functions

Average (A) (M)

Calculates the mean average of the given values.
Syntax
Avg(<numeric Field>)
Arguments

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

Example
AVG(Score)
Returns the mean average of the given scores.

Calculates the average of the given aggregation grouped by another field.
Syntax
Avg(<group-by field>, <aggregation>)
Arguments

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

Example
Avg( Product, Total Sales)
Returns the average of the total sales per product.

Contribution

Calculates the percentage of total.
Syntax
Contribution(<numeric field>)
Arguments

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

Example
Contribution( Total Sales )
Returns 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 (A) (EC)

Returns the correlation coefficient of two numeric fields.
Syntax
CORREL(<Numeric Field a>, <Numeric Field b>)
Arguments

  
Argument Description
<numeric field a>

Any database column containing numeric values.

Note: 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.

Returns the correlation coefficient of two fields aggregations grouped by another field.
Syntax
CORREL(<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
CORREL(Products, AVG(Revenue), AVG(Cost))
Returns the correlation between the average of revenue and cost per product.

Count (A) (M)

Counts the number of unique values within the given values.
Syntax
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.

Dupcount (A) (M)

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.

Covariance (Population) (A) (EC)

Returns the population covariance of <Numeric Field a> and <Numeric Field b>.
Syntax
COVARP(<Numeric Field a>, <Numeric Field b>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

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

<numeric field>

Example
COVARP(Revenue, Cost)
Returns the population covariance of revenue and cost.

Returns the population covariance of two fields aggregations grouped by another field.
Syntax
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.

Covariance (Sample) (A) (EC)

Returns the sample covariance of <Numeric Field a> and <Numeric Field b>.
Syntax
COVAR(<Numeric Field a>, <Numeric Field b>)
Arguments

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

Example
COVAR(Revenue, Cost)
Returns the sample covariance of revenue and cost.

Returns the sample covariance of two fields aggregations grouped by another field.
Syntax
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.

Exponential Distribution (EC)

Returns the exponential distribution for a given value and a supplied distribution parameter lambda.
Syntax
EXPONDIST(<numeric value>, <lambda>, <Cumulative (true/false)>)
Arguments

  
Argument Description
<numeric field> Any database column containing numeric values
<lambda> Any number
<Cumulative>

TRUE = Cumulative distribution function,

FALSE = Probability density function.

Example
EXPONDIST( Count(Leads), 2, False )
Returns the exponential distribution density of the number of leads per country where lambda is 2.

Intercept (EC)

Returns the intercept of a linear regression line through the provided series of x and y values.
Syntax
INTERCEPT(<field>, <numeric field>)
Arguments

  
Argument Description
<field>

Any database column containing numeric values.

Note: 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.

Largest (A)

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> Any number to indicate the ordering of the value in the list of values

Example
LARGEST(<Total Sales>,<3>)
Returns the third-largest Total Sales value.

Maximum (A) (M)

Returns the maximum value among the given values.
Syntax
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.

Median (A) (M)

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.

Minimum (A) (M)

Returns the minimum value among the given values.
Syntax
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.

Mode (A) (M)

Returns the most frequently occurring value from the 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 return the country ID that is the most frequently occurring in the list of items.

Normal Distribution (EC)

Returns the standard normal distribution for a given value, a supplied distribution mean and standard deviation.
Syntax
NORMDIST(SUM(Numeric Field a), <Mean (Numeric Field), All(Numeric Field)>,
<Standard Deviation (Numeric Field), All(Numeric Field)>, <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(Score, ( Mean(Score), All(Score)), ( STDEV(Score), All(Score) ), False )
Returns the normal probability density of a given student score.

Percentile (M)

Returns the k-th percentile value from the given field.
Syntax
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.

Poisson Distribution (EC)

Returns the poisson distribution for a given value and a supplied distribution mean.
Syntax
POISSONDIST( <numeric value>, <mean>, <Cumulative (true/false)&gt
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( Score, ( Mean(Score), All(Score) ), ( STDEV(Score), All(Score) ), False )
Returns the poisson probability density of a given number of scores.

Quartile (M)

Returns the k-th quartile for the given field. Can return minimum value, first quartile, second quartile, third quartile, and max value.
Syntax
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(<Numeric Field>, <k>)
Returns the quartile of the given item.

Rank

Returns the rank of a value in a list of values.
Syntax
RANK(<numeric value>, [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)
Returns the rank of the total annual cost per each product, sorted in ascending order.

Skewness (Population) (A) (EC)

Returns the skewness of the distribution of a given value in the population.
Syntax
SKEWP(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

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

Example
SKEWP(Score)
Returns the skewness of the distribution of scores in the population.

Skewness (Sample) (A) (EC)

Returns the skewness of the distribution of a given value in a sample.
Syntax
SKEW(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

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

Example
SKEW(Score)
Returns the skewness of the distribution of scores in the sample.

Slope (A) (EC)

Returns the slope of a linear regression line through the provided series of x and y values.
Syntax
SLOPE(<field>, <numeric value>)
Arguments

  
Argument Description
<field>

Any database column containing numeric values.

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

<numeric field> 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.

Standard Deviation (Population) (M)

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.
Syntax
STDEVP( <Numeric Value> )
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

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

Example
STDEVP(score)
Returns the Standard Deviation of the given values in the population.

Standard Deviation (Sample) (M)

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.
Syntax
STDEV( <Numeric Value> )
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

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

Example
STDEV(score)
Returns the Standard Deviation of the given values in the sample.

T Distribution (EC)

Returns the student’s T-distribution for a given value and a supplied number of degrees of freedom.
Syntax
TDIST( <numeric value x>,<degrees_freedom>, <Cumulative (true/false)>)
Arguments

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

TRUE = Cumulative Distribution Function

FALSE = Probability Density Function.

Example
TDIST( Score, 3, TRUE )
Returns the student’s T-distribution of a given score, with 3 degrees of freedom.

Variance (Population) (M)

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.
Syntax
VARP( <Numeric Value> )
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

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

Example
VARP( <Grade> )
Returns the variance of grades in the student population.

Variance (Sample) (M)

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.
Syntax
VAR( <Numeric Value> )
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

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

Example
VAR( <Grade> )
Returns the variance of grades in a random sample.

Mathematical Functions

Absolute

Returns the absolute value of the given value.
Syntax
Abs(<Numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
ABS(Cost), where the absolute result for the value ‘2’ or ‘-2’ is ‘2’.

Acos

Returns the angle, in radians, whose cosine is the given numeric expression. Also referred to as arccosine.
Syntax
ACOS(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
ACOS(Total Revenue) will return the angle, in radians, whose cosine is the given total revenue.
For a detailed example of how you can use this function when trying to determine the distance for logistical purposes (i.e., delivery service, flights, the distance between customers, etc.), see here.

Asin

Returns the angle, in radians, whose sine is the given numeric expression. Also referred to as arcsine.
Syntax
ASIN(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
ASIN(Total Revenue) 'o will return the angle, in radians, whose sine is the given total revenue.

Atan

Returns the angle in radians whose tangent is the given numeric expression. Also referred to as arctangent.
Syntax
ATAN(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
ATAN(Total Revenue) will return the angle in radians whose tangent is the given total revenue.

Ceiling

Returns a number rounded up away from zero, to the nearest multiple of significance.
Syntax
CEILING(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
CEILING(Total Cost), where the result of ‘83.2’ is rounded up to ’84’.

Cos

Returns the trigonometric cosine of the given angle (in radians).
Syntax
COS(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
COS(Average Angle) will return the trigonometric cosine of the average angle.

Cosh (EC)

Returns the hyperbolic cosine of the given value.
Syntax
COSH(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
COSH(Total Revenue) will return the hyperbolic cosine of the total revenue.

Cot

Returns the trigonometric cotangent of the given angle (in radians).
Syntax
COT(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
COT(Average Angle) will return the trigonometric cotangent of the average angle.

Exp

Returns the exponential value of the given value.
Syntax
EXP(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
EXP(Sales) will return the exponential value of sales.

Floor

Returns number rounded down, toward zero, to the nearest multiple of ‘1’.
Syntax
FLOOR(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
FLOOR(Revenue), where the result of ‘88.6’ rounded down is ’88’.

Ln

Returns the base-e logarithm of the given value.
Syntax
LN(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
LN(Cost) will return the base e-logarithm of the interest rate.

Log10

Returns the base-10 logarithm of the given value.
Syntax
LOG10(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
LOG10(Revenue) will return the base-10 logarithm of the interest rate.

Mod

Returns the remainder after a number is divided by a divisor.
Syntax
MOD(<numeric value>, divisor)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

divisor Any number you want to divide by.

Example
MOD(Cost, 10), where the reminder of ‘255’ divided by ’10’ is ‘5’.

Power

Returns the results of the given value raised to a supplied power.
Syntax
Power(value, power)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

power Any number you want to raise by the power of.

Example
POWER(Revenue, 2) will return revenue raised by the power of 2.

Quotient

Returns the integer portion of a division.
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), where the integer portion of ‘5’ divided by ‘2’ is ‘2’.

Round

Returns number rounded to a specified number of digits.
ROUND(<numeric value>, num_digits)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

num_digits The number of digits you want to round to.

Example
ROUND(Revenue, 2) will return the revenue rounded to two decimal places.

Sin

Returns the trigonometric sine of the given angle (in radians).
Syntax
SIN(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
SIN(Average Angle) will return the trigonometric sine of the average angle.

Sinh (EC)

Returns the hyperbolic sine of the given value.
Syntax
SINH(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
SINH(Total Revenue) will return the hyperbolic sine of the total revenue.

Square Root

Returns the square root of the given value.
Syntax
SQRT(<Numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values. Accepts only positive values.

Example
SQRT(Cost) will return the square root of cost.

Sum (A) (M)

Calculates the total of the given values.
Syntax
Sum(<Numeric Field>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
Sum(Cost) calculates the total Cost across all items.

Tan

Returns the trigonometric tangent of the given angle (in radians).
Syntax
TAN(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
TAN(Average Angle) will return the trigonometric tangent of the average angle.

Tanh (EC)

Returns the hyperbolic tangent of the given value.
Syntax
TANH(<numeric value>)
Arguments

  
Argument Description
<numeric field>

Any database column containing numeric values.

Example
TANH(Total Revenue) will return the hyperbolic tangent of the total revenue.

Day Difference

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, else 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.

Month Difference 

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, else 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 departures from its departure port to the time of arrival in its destination port. Returns whole numbers.

Quarter Difference

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, else 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.

Year Difference

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, else 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(<Sentence Start>, <Sentence End>)
Returns the difference in years from sentence start to sentence end. Returns whole numbers.

Second Difference

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, else 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.

Minute Difference

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, else 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.

Hour Difference

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, else 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.

Past Week Difference

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, else 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 example, 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)

Past Month Difference

Returns the difference between this month's data and the data from the previous month.
For example, for day resolution: (sales in current day - sales in same day one month back).
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.

Past Quarter Difference

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, else 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 example, 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)

Past Year Difference

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, else 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 example, 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).

Past Period Difference

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 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, else returns null.
Syntax
DiffPastQuarter([Total Sales])
Arguments

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

Example
DiffPastPeriod([Total Sales])
Returns the difference between this period's sales and previous period's sales.

Growth

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

Growth Rate

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

Growth Past Week

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 example, 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)

Growth Past Month

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 example, for day resolution: (sales in current day - sales in same day one month back) / sales in same day one month back.

Growth Past Quarter

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 example, 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.

Growth Past year 

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
GrowthPastWeek([Total Sales])
Calculates the difference between this year's sales and previous year's sales, for the displayed time resolution.
For example, 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).

Prev

Returns the Time period Member in <Time Field> which is N periods back from the current member.
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. For example: If the function is “([Total Quantity], Prev([Months in Date], 2))”, the active time resolution must be ‘months’.
This function can only work as a parameter inside another formula, and not by itself.

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> The number of time periods that we go back.

Example
([Total Quantity], Prev([Months in Date], 2))
This formula returns the Total Quantity value for the month that occurred two months ago.

Next

Returns the value for the time-period member in <Time Field> which is N periods after the current member.
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. For example: If the function is “([Total Quantity],Next([Weeks in Date], 2))”, the active time resolution must be ‘weeks’.
This function can only work as a parameter inside another formula, and not by itself.

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> The number of time periods that we go forward.

Example
([Total Quantity],Next([Months in Date], 2))
This formula returns the Total Quantity value for the month occurring two months ahead.

Now

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.

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

This function can only work as a parameter inside another formula, and not by itself.

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
([Total Quantity],Now([Months in Date]))
This formula returns the Total Quantity value for the current month.

Past Day

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, else returns null.
Syntax
PastDay( <numeric field>, <Numeric of periods>)
Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric values.
<Numeric of periods> (optional) The number of prior periods to use for the calculation.

Example
PastDay(<Total Sales>, 2)
If you’re looking at a specific day, you will see the value for 2 days prior to the day specified.

Past Week

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, else returns null.

Syntax
PastWeek( <numeric field>, <Numeric of periods> )

Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric values.
<Numeric 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’re looking at a specific day, you will see the value of the same day two weeks back.

Past Month

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, else returns null.

Syntax
PastMonth( <numeric field>, <Numeric of periods> )

Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric values.
<Numeric 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’re looking at a specific day, you will see the value of the same day two months back.

Past Quarter

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, else returns null.

Syntax
PastQuarter( <numeric field>, <Numeric of periods> )

Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric values.
<Numeric 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’re looking at a specific day, you will see the value of the same day two quarters back. If you’re looking at a specific month, you will see the value of the same month two quarters back.

Past Year

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, else returns null.

Syntax
PastYear( <numeric field>, <Numeric of periods> )

Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric values.
<Numeric 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’re looking at a specific day, you will see the value of the same day two years back. If you’re looking at a specific month, you will see the value of the same month two years back.

Note:

When using the Past Year function in a weeks table and using a week filter, no results are returned.

Week to Date Average

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.

Week to Date Sum

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.

Month to Date Average

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.

Month to Date Sum

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.

Quarter to Date Average

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.

Quarter to Date Sum

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.

Year to Date Average

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
WTDAvg(<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.

Year to Date Sum

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.

Other Functions

All

Ignores the scope set on the dimension.
This function can only work as part of a measured value, not by itself.

Syntax
All(<Numeric Field>)

Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric data.

Example
Sum(All(Items))
Returns the sum of all items, ignoring filters.

CASE

Returns the result_expression of the first condition evaluated as true. When no condition is true, else_expression is returned, if one is defined.
For example, the below function will return '1' when the Total Sales value is between 100 and 1000. It will return '2' if the Total Sales value is above 1000. It will return '3' in any other case (meaning, when Total Sales are below 100).

Syntax
(WHEN <condition> THEN <result_expression> [...] [ESLE <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
CASE
WHEN Sum(Sales) &lt; 100 THEN 1
WHEN Sum(Sales) &lt; 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).

IF

Returns numeric expression '1' when the condition is true, and expression '2' when the condition is false. Nested conditional statements are supported.

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

Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric data.

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.

IsNull

Returns true if the expression doesn't contain data (Null).

Syntax
(<numeric value>)

Arguments

  
Argument Description
<Numeric Field> Any database column containing numeric data.

Example
Can be used as a condition when writing conditional statements.

Ordering (EC)

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]))

Rdouble (EC)

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

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

For example:`ORDERING([Total Sales], -1*[COUNT Salesman], MIN(<Office Name>))`

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

<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 hasn’t 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 an addition discussion on using RDouble, see here and here.

Rint (EC)

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
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>] )

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.

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

For example:`ORDERING([Total Sales], -1*[COUNT Salesman], MIN(<Office Name>))`

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

<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 hasn’t 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 an addition discussion on using RInt, see here.

Running Sum

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 value> ),
RSUM ( <numeric value> , <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.
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.


Was this article helpful?