Documentation for ProTrackWarehouse 2019.1.
The following is a list of all the functions you can use in the formula editor.
Statistical Functions
| Function | Usage | Syntax | Example | Example Result |
|---|---|---|---|---|
| Average | Calculates the mean average of the given values. | AVG(<numeric field>) | AVG(Score) | Returns the mean average of the given scores. |
| Calculates the average of the given aggregation grouped by another field. | AVG(<group-by field>, <aggregation>) | AVG(Product, Total Sales) | Returns the average of the total sales per product. | |
| Contribution | Calculates the percentage of total. | CONTRIBUTION(<numeric field>) | 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 | Returns the correlation coefficient of two numeric fields. | CORREL(<numeric field a>, <numeric field b>) | CORREL(Revenue, Cost) | Returns the correlation between revenue and cost. |
| Returns the correlation coefficient of two fields aggregations grouped by another field. | CORREL(<group by field>, <aggregation a>, <aggregation b>) | CORREL(Products, AVG(Revenue), AVG(Cost)) | Returns the correlation between the average of revenue and cost per product. | |
| Count | Counts the number of unique values within the given values. | COUNT(<numeric field>) | COUNT([Category ID]) | Returns the number of different category IDs within the given list of items. |
| Count All | Returns the actual item count of the given list of items, including duplicates. | DUPCOUNT(<numeric field>) | DUPCOUNT([Category ID]) | Returns the actual count of category IDs in the list of items. |
| Covariance (Population) | Returns the population covariance of <Numeric Field a> and <Numeric Field b>. | COVARP(<numeric field a>, <numeric field b>) | COVARP(Revenue, Cost) | Returns the population covariance of revenue and cost. |
| Returns the population covariance of two fields aggregations grouped by another field. | COVARP(<group by field>, <aggregation a>, <aggregation b>) | COVARP(Products, AVG(Revenue), AVG(Cost)) | Returns the population covariance of the average revenue and the average cost per product. | |
| Covariance (Sample) | Returns the sample covariance of <Numeric Field a> and <numeric field b>. | COVAR(<numeric field a>, <numeric field b>) | COVAR(Revenue, Cost) | Returns the sample covariance of revenue and cost. |
Returns the sample covariance of two fields aggregations grouped by another field. | COVAR(<group by field>, <aggregation a>, <aggregation b>) | COVAR(Products, AVG(Revenue), AVG(Cost)) | Returns the sample covariance of the average revenue and the average cost per product. | |
Exponential | Returns the exponential distribution for a given value and a supplied distribution parameter lambda. | EXPONDIST(<numeric field>, <lambda>, <Cumulative (true/false)>) | EXPONDIST( Count(Leads), 2, False ) | Returns the exponential distribution density of the number of leads per country where lambda is 2. |
| Intercept | Returns the intercept of a linear regression line through the provided series | INTERCEPT(<field>, <numeric field>) | INTERCEPT(month.int, Total Sales) | Returns the intercept of the regression line that represents the trend of items sold for each month. |
| Largest | Returns the k-th largest value in a field. | LARGEST(<numeric field>, <k>) | LARGEST(Total Sales,3) | Returns the third-largest Total Sales value. |
| Maximum | Returns the maximum value among the given values. | MAX(<numeric field>) | MAX(Total Revenue) | Returns the item with the maximum Total Revenue. |
| Median | Calculates the median of the given values. | MEDIAN(<numeric field>) | MEDIAN(Total Revenue) | Returns the item whose Total Revenue is the middlemost number in the set. |
| Minimum | Returns the minimum value among the given values. | MIN(<numeric field>) | MIN(Total Revenue) | Returns the item with the minimum Total Revenue. |
| Mode | Returns the most frequently occurring value from the column. | MODE(<numeric field>) | MODE(Country ID) | Returns return the country ID that is the most frequently occurring in the list of items. |
| Normal Distribution | Returns the standard normal distribution for a given value, a supplied distribution mean and standard deviation. | NORMDIST(SUM(numeric field a), <Mean (numeric field),All(numeric field)>, <Standard Deviation (numeric field), All(numeric field)>, <Cumulative (true/false)>) | NORMDIST(Score, (Mean(Score), All(Score)), (STDEV(Score), All(Score)), False ) | Returns the normal probability density of a given student score. |
| Percentile | Returns the k-th percentile value from the given field. | PERCENTILE(<numeric field>, <k>) | PERCENTILE(Total Sales, 0.9) | Returns the 90th percentile of Total Sales. |
| Poisson Distribution | Returns the poisson distribution for a given value and a supplied distribution mean. | POISSONDIST(<numeric field>, <mean>, <Cumulative (true/false)>) | POISSONDIST(Score, Mean(Score), False) | Returns the poisson probability density of a given number of scores. |
| Quartile | Returns the k-th quartile for the given field. Can return minimum value, first quartile, second quartile, third quartile, and max value. | QUARTILE(<numeric field>, <k>)
| QUARTILE(Score, 1) | Returns the first quartile (25th percentile) of a given number of scores |
| Rank | Returns the rank of a value in a list of values | RANK(<numeric field>, [DESC/ASC], [Rank Type], | RANK(Total Cost, "ASC", "1224", Product, Years) | Returns the rank of the total annual cost per each product, sorted in ascending order. |
| Skewness | Returns the skewness of the distribution of a given values. | SKEW(<numeric field>) | SKEW(Score) | Returns the skewness of the distribution of scores. |
| Slope | Returns the slope of a linear regression line through the provided series of x and y values. | SLOPE(<field>, <numeric field>) | SLOPE(month.int, Total Sales) | Returns the slope of the regression line that represents a trend of items sold for each month. |
| Standard Deviation | Returns the Standard Deviation of the given values | STDEV(<numeric field>) | STDEV(score) | Returns the Standard Deviation of the given values in the sample. |
| T Distribution | Returns the T-distribution for a given value and a supplied number of degrees of freedom. | TDIST( <numeric field x>,<degrees_freedom>, <Cumulative (true/false)> | TDIST(Score, 3, TRUE) | Returns the T-distribution of a given score, with 3 degrees of freedom. |
| Variance | Returns the Variance of the given values. | VAR(<numeric field>) | VAR(<Grade>) | Returns the variance of grades in a random sample |
Mathematical Functions
| Function | Usage | Syntax | Example | Example Result |
|---|---|---|---|---|
| Absolute | Returns the absolute value of the given value. | ABS(<numeric field>) | ABS(Cost) | Returns the absolute value of the cost. |
| Acos | Returns the angle, in radians, whose cosine is the given numeric expression. | ACOS(<numeric field>) | ACOS(Total Revenue) | Returns the arccosine of the given total revenue. |
| Asin | Returns the angle, in radians, whose sine is the given numeric expression. | ASIN(<numeric field>) | ASIN(Total Revenue) | Returns the arcsine of the given total revenue. |
| Atan | Returns the angle, in radians, whose tangent is the given numeric expression. | ATAN(<numeric field>) | ATAN(Total Revenue) | Returns the arctangent of the given total revenue. |
| Ceiling | Returns a number rounded up away from zero, to the nearest multiple of ‘1’. | CEILING(<numeric field>) | CEILING(Total Cost) | Returns the total cost rounded up. (ex. 83.2 will round up to 84) |
| Cos | Returns the trigonometric cosine of the given angle (in radians). | COS(<numeric field>) | COS(Average Angle) | Returns the cosine of the average angle. |
| Cosh | Returns the hyperbolic cosine of the given value. | COSH(<numeric field>) | COSH(Total Revenue) | Returns the hyperbolic cosine of the total revenue. |
| Cot | Returns the trigonometric cotangent of the given angle (in radians). | COT(<numeric field>) | COT(Average Angle) | Return the trigonometric cotangent of the average angle. |
| Exp | Returns the exponential value of the given value. | EXP(<numeric field>) | EXP(Sales) | Return the exponential value of sales. |
| Floor | Returns number rounded down, toward zero, to the nearest multiple of ‘1’. | FLOOR(<numeric field>) | FLOOR(Revenue) | Returns the total cost rounded down. (ex. 83.8 will round down to 83) |
| Ln | Returns the base-e logarithm of the given value. | LN(<numeric field>) | LN(Cost) | Returns the natural log of the cost. |
| Log10 | Returns the base-10 logarithm of the given value. | LOG10(<numeric field>) | LOG10(Revenue) | Returns the log of the revenue. |
| Mod | Returns the remainder after a number is divided by a divisor. | MOD(<numeric field>, divisor) | MOD(Cost, 10) | Returns "Cost mod 10". Ex. if cost is 52, returns 2. |
| Power | Returns the results of the given value raised to a supplied power. | POWER(<numeric field., power) | POWER(Revenue, 2) | Returns revenue raised by the power of 2. |
| Quotient | Returns the integer portion of a division. | QUOTIENT(<numeric field>, divisor) | QUOTIENT(Cost, 10) | Returns the integer division. |
| Round | Returns number rounded to a specified number of digits. | ROUND(<numeric field>, num_digits) | ROUND(Revenue, 2) | Returns the revenue rounded to two decimal places. |
| Sin | Returns the trigonometric sine of the given angle (in radians). | SIN(<numeric field>) | SIN(Average Angle) | Returns the trigonometric sine of the average angle. |
| Sinh | Returns the hyperbolic sine of the given value. | SINH(<numeric field>) | SINH(Total Revenue) | Returns the hyperbolic sine of the total revenue. |
| Square Root | Returns the square root of the given value. | SQRT(<numeric field>) | SQRT(Cost) | Returns the square root of the cost. |
| Sum | Calculates the total of the given values | SUM(<numeric field>) | SUM(Cost) | Returns the total cost across all items. |
| Tan | Returns the trigonometric tangent of the given angle (in radians). | TAN(<numeric field>) | TAN(Average Angle) | Returns the trigonometric sine of the average angle. |
| Tanh | Returns the hyperbolic tangent of the given value. | TANH(<numeric field>) | TANH(Total Revenue) | Returns the hyperbolic tangent of the total revenue. |
Time-Related Functions
| Function | Usage | Syntax | Example | Example Result |
|---|---|---|---|---|
| Day Difference | Returns the difference between <End Time> and <Start Time> in days | DDIFF(<End Time>, <Start Time>) | 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. | MDIFF(<End Time>, <Start Time>) | MDIFF(Arrival Time, Departure 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. |
| Quarter Difference | Returns the difference between <End Time> and <Start Time> in quarters. Returns whole numbers. | QDIFF(<End Time>, <Start Time>) | QDIFF(End Semester, Start Semester) | Returns the difference in quarters from the first academic semester to the graduation semester. |
| Year Difference | Returns the difference between <End Time> and <Start Time> in years. Returns whole numbers. | YDIFF(<End Time>, <Start Time>) | YDIFF(Sentence End, Sentence Start) | Returns the difference in years from sentence start to sentence end. |
| Second Difference | Returns the difference between <End Time> and <Start Time> in seconds. | SDIFF(<End Time>, <Start Time>) | SDIFF(Leaving Time, Landing 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. | MNDIFF(<End Time>, <Start Time>) | MNDIFF(Payment Completed Time, Landing Time) | Returns the difference in minutes from the time of landing on the page to the time of completing payment. |
| Hour Difference | Returns the difference between <End Time> and <Start Time> in hours. Returns whole numbers. | HDIFF(<End Time>, <Start Time>) | 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. |
| 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. | DIFFPASTWEEK(<numeric field>) | 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. | DIFFPASTMONTH(<numeric field>) | 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. | DIFFPASTQUARTER(<numeric field>) | DIFFPASTQUARTER(Total Sales) | Returns the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution. |
| Past Year Difference | Returns the difference between this year's data and the data from the previous year. | DIFFPASTYEAR(<numeric field>) | DIFFPASTYEAR(Total Sales) | Returns the difference between this year's sales and previous year's sales, for the displayed time resolution. |
| Past Period Difference | Returns the difference between this period's data and the data from the previous period, using the current time resolution. | DIFFPASTPERIOD(<numeric field>) | DIFFPASTPERIOD(Total Sales) | Returns the difference between this period's sales and previous period's sales. |
| Growth | Calculates growth over time. (0% baseline) Accepts any time resolution (day, week, etc.) in the widget. | GROWTH(<numeric field>) | 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. (100% baseline) Accepts any time resolution (day, week, etc.) in the widget. | GROWTHRATE(<numeric field>) | GROWTHRATE(Total Quantity) | If this month your Total Quantity is 12, and last month it was 10, your Growth for this month is 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% (0.8). Calculation: 80 / 100 = 0.8 |
| Growth Past Week | Calculates the growth from the past week to the current week. Use this function when the time resolution in your widget is weeks or days. | GROWTHPASTWEEK(<numeric field>) | GROWTHPASTWEEK(Total Sales) | Calculates the difference between this week's sales and previous week's sales, for the displayed time resolution. |
| Growth Past Month | Calculates the growth from the past month to the current month. Use this function when the time resolution in your widget is month or day. | GROWTHPASTMONTH(<numeric field>) | GROWTHPASTMONTH(Total Sales) | Calculates the difference between this month's sales and previous month's sales, for the displayed time resolution. |
| Growth Past Quarter | Calculates the growth from the past quarter to the current quarter. Use this function when the time resolution in your widget is month or quarter. | GROWTHPASTQUARTER(<numeric field>) | GROWTHPASTQUARTER(Total Sales) | Calculates the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution. |
| Growth Past Year | Calculates the growth from the past year to the current year. | GROWTHPASTYEAR(<numeric field>) | GROWTHPASTYEAR(Total Sales) | Calculates the difference between this year's sales and previous year's sales, for the displayed time resolution. |
| Prev | Returns the Time period Member in <Time Field> which is N periods back from the current member. | ((<numeric field>), PREV(<Time Field>, [<N>])) | ([Total Quantity], PREV([Months in Date], 2)) | 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. | ((<numeric field>), NEXT(<Time Field>, [<N>])) | ([Total Quantity],NEXT([Months in Date], 2)) | Returns the Total Quantity value for the month occurring two months ahead. |
| Now | Returns the value for the current time period. | ((<numeric field>), NOW(<Time Field>)) | ([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. | PASTDAY(<numeric field>) | PASTDAY(Total Sales) | Returns the Total Sales value one day back. |
| Past Week | Returns the value for the same period in the previous week. | PASTWEEK(<numeric field>) | PASTWEEK(Total Sales) | Returns the Total Sales value one week back for the displayed time resolution. |
| Past Month | Returns the value for the same period in the previous month. | PASTMONTH(<numeric field>) | PASTMONTH(Total Sales) | Returns the Total Sales value one month back for the displayed time resolution. |
| Past Quarter | Returns the value for the same period in the previous quarter. | PASTQUARTER(<numeric field>) | PASTQUARTER(Total Sales) | Returns the Total Sales value one quarter back for the displayed time resolution. |
| Past Year | Returns the value for the same period in the previous year. | PASTYEAR(<numeric field>) | PASTYEAR(Total Sales) | Returns the Total Sales value one year back for the displayed time resolution. |
| Range | 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. | RANGE(<Start Date>, <End Date>) | ( [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. |
| Week to Date Average | Returns the running average starting from the beginning of the week up to the current day. | WTDAVG(<numeric field>) | WTDAVG(Total Sales) | Returns the running average of Total Sales starting from the beginning of the week up to the current day. |
| Week to Date Sum | Returns the running total starting from the beginning of the week up to the current day or week. | WTDSUM(<numeric field>) | 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. | MTDAVG(<numeric field>) | MTDAVG(Total Sales) | Returns the running average of Total Sales 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 or week. | MTDSUM(<numeric field>) | MTDSUM(Total Sales) | 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. | QTDAVG(<numeric field>) | QTDAVG(Total Sales) | Returns the running average of Total Sales starting from the beginning of the quarter up to the current day. |
| Quarter to Date Sum | Returns the running total starting from the beginning of the quarter up to the current day or week. | QTDSUM(<numeric field>) | QTDSUM(Total Sales) | Returns the running total of Total Sales starting from the beginning of the quarter up to the current day. |
| Year to Date Average | Returns the running average starting from the beginning of the year up to the current day. | YTDAVG(<numeric field>) | YTDAVG(Total Sales) | Returns the running average of Total Sales starting from the beginning of the year up to the current day. |
| Year to Date Sum | Returns the running total starting from the beginning of the year up to the current day or week. | YTDSUM(<numeric field>) | YTDSUM(Total Sales) | Returns the running total of Total Sales starting from the beginning of the year up to the current day. |
Other Functions
| Function | Usage | Syntax | Example | Example Result |
|---|---|---|---|---|
| All | Ignores the scope set on the dimension. This function can only work as a parameter inside another formula, and not by itself. | ALL(<numeric field>) | 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. | (WHEN <condition> THEN <result_expression> [ELSE <result_expression>] END) | CASE WHEN SUM(Sales) < 100 THEN 1 WHEN SUM(Sales) < 1000 THEN 2 ELSE 3 END | Returns '1' when Total Sales is less than 100, Returns '2' when Total Sales is less than 1000, Returns '3' in any other case |
| If | Returns numeric expression '1' when the condition is true, and expression '2' when the condition is false. | IF (<condition>, <numeric expression 1>, <numeric expression 2>) | 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 Total Sales x 1.1. Otherwise, returns only the Total Sales |
| IsNull | Returns true if the expression doesn't contain data (Null). | ISNULL(<numeric field>) | IF(ISNULL(Cost), 0, Cost) | If the Cost is null, returns 0. Otherwise, returns the cost. |
| Ordering | 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. | ORDERING(<expression1>,<expression2>) | ORDERING(MIN([Sales Person Name]), MIN([Days in Transaction_Date]), Sum([Sales])) | Returns the sum of sales, ordered by sales person descending, breaking ties with days in transaction date. |
Overview
Content Tools
Copyright © 2015 - present. TZA Confidential. All rights reserved.