Function reference

Calculates the average of the given values.

Syntax

AVG(<numeric field>)

Arguments

Example

AVG(Score)

Returns the mean average of the given scores.

Suggested widget types

Indicator

Aggregative information

Note: The aggregative section below does not work with the ESO data model.

Calculates the average of the given aggregation grouped by another field.

Syntax

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

Arguments

Example

Returns the average of the total sales per product.

Calculates the percentage of total.

Syntax

CONTRIBUTION(<numeric field>)

Arguments

Example

CONTRIBUTION(DUPCOUNT([Aiding Agency]))

Returns the percentage of incidents per aiding agency out of total incidents.

Suggested widget types

Pivot table, column chart, bar chart, pie chart

Unsupported widget types

Indicator

Counts the number of unique values within the given values. Contrast this function to the “Count All” function below.

Syntax

COUNT(<numeric field>)

Arguments

Example

COUNT([Gender])

Returns the number of different genders within the given list of items. In most cases, this means the value of 3 (“male”, “female”, “not reported”) would be returned, even if there are thousands of records.

Returns the actual item count of the given list of items, including duplicates. Contrast this function to the “Count” function above.

Syntax

DUPCOUNT(<numeric field>)

Arguments

Example

DUPCOUNT([Gender])

Returns the actual count of rows containing a Gender value in the list of items. For example, if your data contains 934 patient, and you use DUPCOUNT on a gender field, then you would return a value of 934.

Returns the maximum value among the given values.

Syntax

MAX(<numeric field>)

Arguments

Example

MAX([Total Revenue])

Returns the item with the maximum Total Revenue.

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

Example

MEDIAN([Total Revenue])

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

Returns the minimum value among the given values.

Syntax

MIN(<numeric field>)

Arguments

Example

MIN([Total Revenue])

Returns the item with the minimum Total Revenue.

Returns the k-th percentile value from the given field. See also the Quartile function below.

Syntax

PERCENTILE(<numeric field>, <k>)

Arguments

Example

PERCENTILE([Chute Time], <0.9>)

Returns the 90th percentile of Chute Time.

Returns the k-th quartile for the given field. Can return minimum value, first quartile, second quartile, third quartile, and max value. See also the Percentile function above.

Syntax

QUARTILE(<numeric field>, <k>)

Arguments

Example

QUARTILE(<numeric field>, <k>))

Returns the quartile of the given item.

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

Syntax

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

Arguments

Example

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

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

Unsupported widgets

Indicator, Table

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

Arguments

Example

STDEVP(score)

Returns the standard deviation of the given values in the population.

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

Arguments

Example

STDEV(score)

Returns the standard deviation of the given values in the sample.

Returns the absolute value of the given value.

Syntax

ABS(<numeric field>)

Arguments

Example

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

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

Syntax

ACOS(<numeric field>)

Arguments

Example

ACOS(Total Revenue)

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

Returns the angle, in radians, whose sine is the given numeric expression. Also referred to as arccosine.

Syntax

ASIN(<numeric field>)

Arguments

Example

ASIN(Total Revenue)

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

Returns the angle, in radians, whose tangent is the given numeric expression. Also referred to as arctangent.

Syntax

ATAN(<numeric field>)

Arguments

Example

ATAN(Total Revenue)

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

Returns a number rounded up away from zero, to the nearest multiple of significance. See also: the ROUND, and FLOOR functions below.

Syntax

CEILING(<numeric field>)

Arguments

Example

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

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

Syntax

COS(<numeric field>)

Arguments

Example

COS(Average Angle)

Returns the trigonometric cosine of the average angle.

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

Syntax

COSH(<numeric field>)

Arguments

Example

COSH(Total Revenue)

Returns the hyperbolic cosine of the total revenue.

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

Syntax

COT(<numeric field>)

Arguments

Example

COT(Average Angle)

Returns the trigonometric cotangent of the average angle.

Returns the exponential value of the given value.

Syntax

EXP(<numeric field>)

Arguments

Example

EXP(Sales)

Returns the exponential value of sales.

Returns number rounded down, toward zero, to the nearest multiple of ‘1’. Functionally equivalent to rounding down. See also the ROUND and CEILING functions.

Syntax

FLOOR(<numeric field>)

Arguments

Example

FLOOR(Revenue)

Returns the result of ‘88.6’ rounded down is ’88’.

Returns the base-e logarithm (natural log) of the given value.

Syntax

LN(<numeric field>)

Arguments

Example

LN(Cost)

Returns the base e-logarithm of the interest rate.

Returns the base-10 logarithm of the given value.

Syntax

LOG10(<numeric field>)

Arguments

Example

LOG10(Cost)

Returns the base-10 logarithm of the interest rate.

Returns the remainder after a number is divided by a divisor.

Syntax

MOD(<numeric field>, divisor)

Arguments

Example

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

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

Syntax

Power(<numeric field>, power)

Arguments

Example

POWER(Revenue, 2),

Returns the revenue raised by the power of 2 (Revenue²).

Returns the integer portion of a division.

Syntax

QUOTIENT(<numeric field>, divisor)

Arguments

Example

QUOTIENT(Cost, 2),

Returns the integer portion of ‘5’ divided by ‘2’ is ‘2’.

Returns the number rounded to a specified number of digits. See also: CEILING and FLOOR functions.

Syntax

ROUND(<numeric field>, num_digits)

Arguments

Example

ROUND(Revenue, 2),

Returns the revenue rounded to two decimal places.

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

Syntax

SIN(<numeric field>)

Arguments

Example

SIN(Average Angle),

Returns the trigonometric sine of the average angle.

Returns the hyperbolic sine of the given value.

Syntax

SINH(<numeric field>)

Arguments

Example

SINH(Total Revenue),

Returns the hyperbolic sine of the total revenue.

Returns the square root of the given value.

Syntax

SQRT(<numeric field>)

Arguments

Example

SQRT(Cost),

Returns the square root of cost (√Cost).

Calculates the total of the given values.

Syntax

SUM(<numeric field>)

Arguments

Example

SUM(Cost), calculates the total Cost across all items.

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

Syntax

TAN(<numeric field>)

Arguments

Example

TAN(Average Angle)

Returns the trigonometric tangent of the average angle.

Returns the hyperbolic tangent of the given value.

Syntax

TANH(<numeric field>)

Arguments

Example

TAN(Average Angle)

Returns the hyperbolic tangent of the total revenue.

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

Syntax

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

Arguments

Example

DDiff(<Discharge Time>, <Admission Time>)

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

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

Syntax

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

Arguments

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.

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

Syntax

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

Arguments

Example

QDiff(<StartSemester>, <EndSemester>)

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

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

Syntax

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

Arguments

Example

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

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

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

Syntax

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

Arguments

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.

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

Syntax

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

Arguments

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.

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

Syntax

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

Arguments

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.

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

Note: Use this function when the time resolution used in your widget is day or week; otherwise, the widget does not display correct data.

Syntax

DiffPastWeek(<numeric field>)

Arguments

Example

DiffPastWeek([Total Sales])

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

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)

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

Note: Use this function when the time resolution used in your widget is month; otherwise, the widget does not display correct data.

Syntax

DiffPastMonth(<numeric field>)

Arguments

Example

DiffPastMonth([Total Sales])

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

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

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

Note: Use this function when the time resolution used in your widget is month or quarter; otherwise, the widget does not display correct data.

Syntax

DiffPastQuarter(<numeric field>)

Arguments

Example

DiffPastQuarter([Total Sales])

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

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)

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

Note: All time resolutions in the widget are available for this function (year, quarter, month, week, day).

Syntax

DiffPastYear(<numeric field>)

Arguments

Example

DiffPastYear([Total Sales])

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

Example: For month resolution: (sales in current month - sales in same month one year back). For quarter resolution: (sales in current quarter- sales in previous quarter one year back). For week resolution: (sales in current week - sales in same week one year back).

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

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

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

Syntax

DiffPastPeriod(<numeric field>)

Arguments

Example

DiffPastPeriod([Total Sales])

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

Calculates growth over time.

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

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

Syntax

Growth(<numeric field>)

Arguments

Example

Growth([Total Quantity])

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

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

Calculates growth over time.

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

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

Syntax

GrowthRate(<numeric field>)

Arguments

Example

GrowthRate([Total Quantity])

If this month your Total Quantity is 12, and last month it was 10, your GrowthRate 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 GrowthRate for this year is 80/100 = 80% ( 0.8). Calculation: 80 / 100 = 0.8

Calculates the growth from the past week to the current week.

Note: Use this function when the time resolution used in your widget is weeks or days; otherwise, the widget does not display any data.

Syntax

GrowthPastWeek(<numeric field>)

Arguments

Example

GrowthPastWeek([Total Sales])

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

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)

Calculates the growth from the past month to the current month.

Note: Use this function when the time resolution used in your widget is month or day, otherwise, the widget does not display any data.

Syntax

GrowthPastMonth(<numeric field>)

Arguments

Example

GrowthPastMonth([Total Sales])

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

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

Calculates the growth from the past quarter to the current quarter.

Note: Use this function when the time resolution used in your widget is month or quarter. otherwise, the widget does not display any data.

Syntax

GrowthPastQuarter(<numeric field>)

Arguments

Example

GrowthPastQuarter([Total Sales])

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

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.

Calculates the growth from the past year to the current year.

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

Syntax

GrowthPastYear(<numeric field>)

Arguments

Example

GrowthPastYear([Total Sales])

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

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

Returns the time-period member in <time field> , which is <n> periods back from the current member.

Syntax

((<<numeric field>>), Prev(<time field> , [<n> ]))

Arguments

Example

([Total Quantity], Prev([Months in Date], 2))

Returns the Total Quantity value for the month that occurred two months ago.

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

Syntax

((<<numeric field>>), Next(<time field> , [<n> ]))

Arguments

Example

([Total Quantity], Next([Months in Date], 2))

Returns the Total Quantity value for the month occurring two months ahead.

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.

Syntax

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

Arguments

Example

([Total Quantity], Now([Months in Date]))

Returns the Total Quantity value for the current month.

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

Syntax

PastDay(<numeric field>)

Arguments

Example

PastDay(<Total Sales>)

If you are looking at a specific day, you will see the value one day back.

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

Syntax

PastWeek(<numeric field>)

Arguments

Example

PastWeek(<Total Sales>)

Returns the Total Sales value one week back for the displayed time resolution.

If you are looking at a specific day, you will see the value one week back.

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

Syntax

PastMonth(<numeric field>)

Arguments

Example

PastMonth(<Total Sales>)

Returns the Total Sales value one month back for the displayed time resolution.

If you are looking at a specific day, you will see the value one month back.

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

Syntax

PastQuarter(<numeric field>)

Arguments

Example

PastQuarter(<Total Sales>)

Returns the Total Sales value one quarter back for the displayed time resolution.

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

Returns the value for the same period in the previous year. Accepts any time (day, week, and so forth).

Note: If you use the Past Year function in a weeks table and also use a week filter, no results return.

Syntax

PastYear(<numeric field>)

Arguments

Example

PastYear(<Total Sales>)

Returns the Total Sales value one year back for the displayed time resolution.

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

Returns an accumulated value for a data set where two members of the same dimension and level define the minimum and maximum values of the range. Accepts the time resolutions of day and month.

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

Syntax

Range(<start date>, <end date>)

Arguments

Example

([Total Quantity], RANGE ( PREV ( [Days in Date],2), current( [Days in Date] )))

Returns the Total Quantity value in the range between two days ago and today.

Returns the running average starting from the beginning of the week up to the current day. Returns null if the active time resolution is years, quarters, or months.

Syntax

WTDAvg(<numeric field>)

Arguments

Example

WTDAvg(<Total Sales>)

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

Returns the running total starting from the beginning of the week up to the current day or week. Returns null if the active time resolution is years, quarters, or months.

Syntax

WTDSum(<numeric field>)

Arguments

Example

WTDSum(<Total Sales>)

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

Returns the running average starting from the beginning of the month up to the current day. Returns null if the active time resolution is years, quarters, or weeks.

Note: Use this function when the active time resolution in your widget is days.

Syntax

MTDAvg(<numeric field>)

Arguments

Example

MTDAvg(<Total Sales>)

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

Returns the running total starting from the beginning of the week up to the current day or week. Returns null if the active time resolution is years, quarters, or months.

Syntax

MTDSum(<numeric field>)

Arguments

Example

MTDSum(<Total Sales>)

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

Returns the running average starting from the beginning of the quarter up to the current day or month. Returns null if the active time resolution is weeks.

Syntax

QTDAvg(<numeric field>)

Arguments

Example

QTDAvg(<Total Sales>)

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

Returns the running total starting from the beginning of the quarter up to the current day or month. Returns null if the active time resolution is weeks.

Syntax

QTDSum(<numeric field>)

Arguments

Example

QTDSum(<Total Sales>)

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

Returns the running average starting from the beginning of the year up to the current day, week, month, quarter, or year. Returns null if the query is invalid, or returns no results.

Syntax

YTDAvg(<numeric field>)

Arguments

Example

YTDAvg(<Total Sales>)

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

Returns the running total starting from the beginning of the year up to the current day, week, month, quarter, or year. Returns null if the query is invalid, or returns no results.

Syntax

YTDSum(<numeric field>)

Arguments

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.

Ignores the scope set on the dimension.

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

Syntax

All(<numeric field>)

Arguments

Example

Sum(All(Items))

Returns the sum of all items, ignoring filters.

Returns the result_expression of the first condition evaluated as true. When no condition is true, else_expression is returned, if one is defined.

Syntax

(WHEN <condition> THEN <result_expression> [...] [ESLE <result_expression>] END)

Arguments

Example

CASE
WHEN Sum(Sales) < 100 THEN 1
WHEN Sum(Sales) < 1000 THEN 2
ELSE 3
END)

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

Returns the 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

Example

IF(Count(Sales)>100, Sum(Sales)*1.1, sum(Sales))

If the number of unique values within Sales is larger than 100, returns the Total Sales x 1.1 (sales increase of 10%). If the number of unique values within values is lower than 100, returns only the Total Sales, without an increase.

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

Note: You can use this value as a condition when writing conditional statements.

Syntax

(<numeric field>)

Arguments

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

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

Syntax

ORDERING(<expression1>,<expression2>)

Arguments

Example

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

Returns a numeric result for a given <R expression> and a list of numeric values. The <R expression> is passed to the running <Rserve>.

Syntax

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

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

Arguments

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]

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 field> 1>, [<<numeric field> 2>, ..., <<numeric field><n> >])

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

Arguments

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]

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.

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

Syntax

RSUM(<numeric field>)

RSUM(<numeric field>), <continuous>

Arguments

Example

RSUM([Total Revenue], FALSE)

Returns the running total of the Total Revenue measure.